Query output filtered

[santosm]

Hi All,
I am trying to run a query here and have it output to a file on my network. The error that pops up is 3011 saying that it can't find the object "select * from..." I am sure i have something incorrect on the outputto command.

Code: Select all

strWhere = "IN (""AL"",""AK"",""AZ"")"
strLoc = "O:\CSDB\REPORTS\SalesReports\custbystate.xls"
sQuery = "select * from [qCustomerByState] where [STATE_24] " & strWhere
DoCmd.OutputTo acOutputQuery, sQuery, acFormatXLS, strLoc

Thanks,
Mark

[Rudi]

Try this:

Code: Select all

sQuery = "select * from [qCustomerByState] where [STATE_24] ='" & strWhere & "';"

or

Code: Select all

sQuery = "select * from [qCustomerByState] where [STATE_24] =" & Chr(34) & strWhere & Chr(34) & ";"

[Pat]

That won't work Rudi, because the WHERE clause has already got the IN condition, it doesn't need the = as well.

I would change the command:
strWhere = "IN (""AL"",""AK"",""AZ"")"
to:
strWhere = "IN ('AL','AK','AZ')"

In other word use ' instead of " in the IN condition.

[santosm]

Thanks again guys. I did get this working but the code was failing because of something in the query pointing to the SQL tables. Now that it is straightened out, it is all good.

Thanks,
Mark