Access Microsoft Access - SQL Error - Expexted: End of Statement

[calmypal]

I can't find the error in the string. Can anyone help?

PStartDate is a date type and PMyCat is a number type

strSQL = "SELECT * FROM qryMembers WHERE [MyScanInDate] = # " & PStartDate & " # And [CategoryID] = " & PMyCat ORDER BY [MemberID], [ScanInDate], [Flag]"

 

[knight00779]

Hi,

strSQL = "SELECT * FROM qryMembers WHERE [MyScanInDate] = " & PStartDate & " And [CategoryID] = " & PMyCat & "ORDER BY [MemberID], [ScanInDate], [Flag]"

According to me this should be the syntax. you missed on on quotes before "Order" and not sure what were those " # " so have eliminated them.

Hope this helps

rgrds
Manohar

 

[chaver]

Hi

It's a long time since your post but here is my input in case you still have a problem.

strSQL = "SELECT * FROM qryMembers WHERE [MyScanInDate] = #" & Format(PStartDate,"mm/dd/yyyy") & "# And [CategoryID] = " & Format(PMyCat) & " ORDER BY [MemberID], [ScanInDate], [Flag];"

Note that everything you give to an SQL statement must be text. So PMyCat needs to be set as Format(PMyCat) to make it text. If you are selecting a value off a form then you can use Me!PMyCat (if that is a field on your form) and even if it contains a number, Me!PMyCat is treated as Text. Also, irrespective of how your PC is set on its date format (in our case usually European), SQL always expects the date in US format i.e. mm/dd/yyyy. You were right, by the way, to include the date between # characters but you shouldn't include any spaces. The semicolon ( on the end is usually optional but it shows the statement has finished.