Invalid SQL Statement Expected 'Select'

[gmann]

Hello,

I'm having trouble getting this sql statement to work in VBA.

strSQL = "SELECT Projects.siteid, Stations.[sequence numer], Stations.Station, Chemical.Details, Stations.Elevation, Stations.RiserLength, Stations.Screen_Length, Chemical.Date_Collected, Chemical.[Time Collected], Chemical.Lab, Chemical.GW, Chemical_Details.parameter, Chemical_Details.result, Chemical_Details.[reporting limit], Chemical_Details.units, IIf([result]=0," < " & [reporting limit],[result]) AS Report_Result, Chemical.Matrix, Stations.Station_Description, Chemical.Chemical_DataID, Chemical.Dup, Projects.address1, Projects.city, Projects.zip, Projects.spillnum, Projects.state, Chemical.Method, Stations.Station_Description" & _
" FROM (Projects INNER JOIN Stations ON Projects.siteid = Stations.SiteID) INNER JOIN (Chemical INNER JOIN Chemical_Details ON Chemical.Chemical_DataID = Chemical_Details.Chemical_DataID) ON Stations.StationID = Chemical.StationID" & _
" WHERE (((Projects.siteid)=[Forms]![ExportData]![Combo10]) AND ((Stations.Station) Like '*" & [Forms]![ExportData]![Combo37] & "*' ) AND ((Chemical.Date_Collected) Between [Forms]![ExportData]![DateFromc] And [Forms]![ExportData]![DateTillc]) AND ((Chemical.Lab)=[Forms]![ExportData]![Combo18]) AND ((Chemical.Matrix)=[Forms]![ExportData]![Combo20]) AND ((Chemical.Dup)=False) AND ((Chemical.Method) Like '*" & [Forms]![ExportData]![Combo31] & "*'))" & _
" ORDER BY Stations.[sequence numer], Stations.Station, Chemical.Chemical_DataID;"

On Error Resume Next
dbs.QueryDefs.Delete "qryExportData"
On Error GoTo 0
Set query = dbs.CreateQueryDef("qryExportData")
query.SQL = strSQL


The actual error comes on the query.SQL=strSQL line

Run-Time Error 3129
Invalid SQL Statement; Expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update'

Any Suggestion are greatly apreciated.

Thanks
Greg

 

[fuzzygeek]

try some debugging

After your strsql statement, put the following code in:
debug.print strsql

This will let you see what your sql statement really looks like. I believe that you are having string and number problems with your string. The following are examples in handling numbers and strings (such as the controls that you are using within the string).

'String example
"[LastName] = '" _
& Forms!Employees!LastName & "'")

'Number example
"[EmployeeID] = " _
& Forms!Orders!EmployeeID)

The IIF isn't parsed correctly and needs to be removed until you get the rest working, then debug the IIF statement.