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

 

[RoryA]

I don't see how you could get that error on a String assignment, assuming sSQL is a simple string variable and not a class property.

 

[ChrisBrook]

It's specifically highlighting Format

It's confusing the heck out of me!

 

[RoryA]

Did you create a routine/module/property called Format somewhere?

 

[ChrisBrook]

So I've been having a tweak and removed the format section.
Currently StartDate = 12/05/2016
and EndDate = 13/05/2016

I've altered the query to:

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  > " & StartDate & ") AND (tbl_Data.Date < " & EndDate & ") ORDER BY tbl_Queues.Queue_ID, tbl_Data.Date"

However I'm now getting the following

An Error has occured. Please find the details below.
Error Number: -2147217900
Description: Syntax error (missing operator) in query expression '(tbl_Data.Date > 12/05/2016) AND (tbl_Data.Date < 13/05/2016) ORDER BY tbl_Queues.Queue_ID, tbl_Data.Date.

Am I missing something obvious?

 

[RoryA]

You still need to enclose the dates in # marks:
(tbl_Data.Date > #12/05/2016#) AND (tbl_Data.Date < #13/05/2016#)

 

[ChrisBrook]

Morning,

I've adjusted it now to:

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  > #" & StartDate & "#) AND (tbl_Data.Date < #" & EndDate & "#)) ORDER BY tbl_Queues.Queue_ID, tbl_Data.Date"

However now get no records returned when I know records are available.

 

[RoryA]

Are you passing dates that could be interpreted differently in US format? You really should be formatting them as mm/dd/yyyy - figuring out why Format was causing you an error is a better option than simply removing it from your code.

 

[ChrisBrook]

Strangely I've left the code as per the original and just added vba.format() rather than format() and it's worked a charm.

Any idea why this is the case?

 

[RoryA]

As I mentioned before, you have created something called Format - a routine/module/variable.