SQL Statement

Topic

New Reply

Good Afternoon,

I’m struggling to find out what is wrong with my SQL syntax ( ). Can someone help find my error?

[codebox]
dCLD = DMax(“LoadDate”, “tbLoadDate”)

strData = “SELECT tbl_CompleteList.fkUserID ” & _
“FROM tbl_CompleteList ” & _
“WHERE (tbl_CompleteList.fkUserID Is Null AND tbl_CompleteList.nClosed=0 AND (tbl_CompleteList.Load_Date < dcld));"[/codebox]

I've also tried:

[codebox]
strData = "SELECT tbl_CompleteList.fkUserID " & _
"FROM tbl_CompleteList " & _
"WHERE (((tbl_CompleteList.fkUserID) Is Null) AND ((tbl_CompleteList.nClosed)=0) AND ((tbl_CompleteList.Load_Date) < dcld));"[/codebox]

When I run either one, the dreaded "Run Time-Erro 3061: Too few parameters. Expected 1", error message is returned.
I appreciate any help.

Reply | Quote

Replies

You cannot use a VBA variable within an SQL string – you must concatenate the SQL string with its value. Also, date values must be enclosed in # characters:

strData = “SELECT fkUserID FROM tbl_CompleteList ” & _
“WHERE fkUserID Is Null AND nClosed=0 AND ” & _
“Load_Date < #" & dCLD & "#"

If there is a chance that your database will be used by someone with non-US date settings, you must convert the date to mm/dd/yyyy format:

strData = "SELECT fkUserID FROM tbl_CompleteList " & _
"WHERE fkUserID Is Null AND nClosed=0 AND " & _
"Load_Date < #" & Format(dCLD, "mm/dd/yyyy") & "#"

Reply | Quote

Thanks Hans,

That works….I don’t know why I insist on overlooking the obvious and making my work harder than it needs to be.

Much appreciated.

Reply | Quote