SQL syntax

[jgelpi16]

I'm getting the "Too few parameters, expected 1" error. Can someone look at my SQL to make sure it's in proper order?

mySQL = "SELECT [WE Date] AS WeekEnd, Store, [Returns Recycles] AS Backhaul, [# of Cases] AS NumCases," & _
            "[Total Time to Unload] AS UnloadTime, [# of Team Members Unloading] AS NumUnloading," & _
            "[Cartons per Hour (CPH)] AS CPH FROM FINAL_OTM_ODS_UPLOAD WHERE [Store] = " & lngStore & " AND [WeekEnd] = #" & dtWeek & "#;"

Thanks in advance.

[ajetrumpet]

why do you have an SQL variable set to a select statement? the only thing you can do with a select statement in vba is open up a recordset.

other than that, I don't see any errors. Other than that access might not like the # signs? or () sings inside of field names?? is the 'AS' keyword allowed in vba?

it's always a good idea never to have spaces in field names. replace them with _'s

[jgelpi16]

Yea, unfortunately the source table is not mine and I did not have control over the design. My intent is to use it in a recordset. I figured out the error and it kind of perplexes me. In my week criteria I had to use the actual field name, not the variable name.

mySQL = "SELECT [WE Date] AS WeekEnd, Store, [Returns Recycles] AS Backhaul, [# of Cases] AS NumCases," & _
"[Total Time to Unload] AS UnloadTime, [# of Team Members Unloading] AS NumUnloading," & _
"[Cartons per Hour (CPH)] AS CPH FROM FINAL_OTM_ODS_UPLOAD WHERE [Store] = " & lngStore & " AND [WE Date] = #" & dtWeek & "#;"

Thanks for your help!