SQL query returning wrong number of arguments or invalid property assignment

[ChrisBrook]

Hi,

I'm currently building an MI report in MS Excel from a MS access database, everything was working fine and then all of a sudden I start getting the following error:
'Wrong number of arguments or invalid property assignment'

Nothing has changed in the query I'm running. Below is a little bit of detail.

I have an Excel spreadsheet with 4 sheets 'Front', 'All Queues', 'Data' & 'Backlog'.

I then have three modules: DBConnect, Module1 & Module2
I'm setting a start date and end date on the front sheet and then clicking a command button. This then calls the following:

RetrieveBackLogData (This works fine)
RetrieveAStarData Sheets("Front").Cells(6, 9).Value, Sheets("Front").Cells(7, 9).Value
This goes off to Module1 as follows:
Public Sub RetrieveAStarData(StartDate As Date, EndDate As Date)

Sheets("Data").Range("A2", "AA" & Sheets("Data").Range("A2").End(xlDown).Row).Clear
OpenDataBase DataBasePath, DataBasePassword


[COLOR=#ff0000]sSQL = "SELECT tbl_Data.Queue_ID, tbl_Queues.Name, tbl_Data.Date, tbl_Heritages.Name, tbl_Teams.Name, tbl_Buckets.Name, SLA, SMV, Incoming, Outstanding FROM tbl_Teams INNER JOIN ((tbl_Buckets INNER JOIN (tbl_Heritages INNER JOIN tbl_Queues ON tbl_Heritages.Heritage_ID = tbl_Queues.Heritage) ON tbl_Buckets.Bucket_ID = tbl_Queues.Bucket) INNER JOIN tbl_Data ON tbl_Queues.Queue_ID = tbl_Data.Queue_ID) ON tbl_Teams.Team_ID = tbl_Queues.Team WHERE ((tbl_Data.Date  > #" & Format(DateAdd("d", -1, StartDate), "mm/dd/yyyy") & "#) AND (tbl_Data.Date < #" & Format(DateAdd("d", 1, EndDate), "mm/dd/yyyy") & "#)) ORDER BY tbl_Queues.Queue_ID, tbl_Data.Date"[/COLOR]


RetrieveDataBaseData (sSQL)
If RecordSet.EOF Or RecordSet.BOF Then
    MsgBox ("No Records Found")
    End
End If
Sheets("Data").Range("A2").CopyFromRecordset RecordSet
RecordSet.Close
DataBaseConnection.Close
End Sub

When It gets to the highlighted line I get the error.
Any suggestions?

Thanks
Chris

 

[Kyle123]

@Rorya

You really should be formatting them as mm/dd/yyyy

You should really be formatting them as yyyy/mm/dd :P

 

[ssingh75]

@Rorya

You should really be formatting them as yyyy/mm/dd :P

Also, can u pls check from where this fields are coming...there is no table name mentioned for them
--SLA, SMV, Incoming, Outstanding -------


sSQL= "SELECT "
sSQL=sSQL & "tbl_Data.Queue_ID, tbl_Queues.Name, "
sSQL=sSQL & "tbl_Data.Date, tbl_Heritages.Name, tbl_Teams.Name, tbl_Buckets.Name, "
sSQL=sSQL & "SLA, SMV, Incoming, Outstanding "
sSQL=sSQL & "FROM "
sSQL=sSQL & "tbl_Teams "
sSQL=sSQL & "INNER JOIN "
sSQL=sSQL & "((tbl_Buckets INNER JOIN (tbl_Heritages INNER JOIN tbl_Queues "
sSQL=sSQL & "ON "
sSQL=sSQL & "tbl_Heritages.Heritage_ID = tbl_Queues.Heritage) "
sSQL=sSQL & "ON "
sSQL=sSQL & "tbl_Buckets.Bucket_ID = tbl_Queues.Bucket) "
sSQL=sSQL & "INNER JOIN "
sSQL=sSQL & "tbl_Data "
sSQL=sSQL & "ON "
sSQL=sSQL & "tbl_Queues.Queue_ID = tbl_Data.Queue_ID) "
sSQL=sSQL & "ON "
sSQL=sSQL & "tbl_Teams.Team_ID = tbl_Queues.Team "
sSQL=sSQL & "WHERE "
sSQL=sSQL & "((tbl_Data.Date > #" & Format(DateAdd("d", -1, StartDate), "mm/dd/yyyy") & "#) "
sSQL=sSQL & "AND "
sSQL=sSQL & "(tbl_Data.Date < #" & Format(DateAdd("d", 1, EndDate), "mm/dd/yyyy") & "#)) "
sSQL=sSQL & "ORDER BY "
sSQL=sSQL & "tbl_Queues.Queue_ID, tbl_Data.Date"