Query OutputTo

[santosm]

Hi All,
I have a query that I want exported as an XLS doc. I am trying this but it doesn't work:

Code: Select all

        
strReport = "select * from Assigned_que_email where [group] = " & intLocation
DoCmd.OutputTo acOutputQuery, strReport, acFormatXLS, strLoc

I am pretty sure I have it wrong where you can't call the query that way in a OutputTo statement. What would be the best way to do this considering the query must be filtered on a variable (intLocation)?

Thanks,
Mark

[HansV]

Indeed, you cannot use a SQL statement in DoCmd.OutputTo acOutputQuery. You have to refer to a saved query.
You could create and save a query that selects * (all fields) from Assigned_que_email. Let's say that you name it qryAssigned_que_email.
You can then use

Dim strQueryName As String
strQueryName = "qryAssigned_que_email"
CurrentDb.QueryDefs(strQueryName).SQL = "select * from Assigned_que_email where [group] = " & intLocation
DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, strLoc

[santosm]

Thanks Hans! So I got to learn yet another new thing today!