RE: [MS_AccessPros] strSQL issues with filtering

Patty:
It is not working because DoCmd.OutputTo acQuery expects a query name; your SQL string ('strSQL = "qryListOfCopiers_Xerox_Survey where [OfficeID] = " & strId & ")" is not an actual query name.
If you create a query (ex "qryTemp"), and assign it the SQL string: "SELECT * FROM qryListOfCopiers_Xerox_Survey where [OfficeID] = " & strId & ",
then you can export qryTemp to Excel with DoCmd:
>>> DoCmd.OutputTo acQuery, "qryTemp", "MicrosoftExcel(*.xls)", outputFileName, False, ""
hth
Steve Conklin






________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Tue, 25 Nov 2014 10:52:29 -0500
> Subject: [MS_AccessPros] strSQL issues with filtering
>
>
>
> I have the code below where I am trying to create multiple excel files,
> filtered by an OfficeID. They have distinct titles and each file
> should have all the records for a particular OfficeID.
>
> I can export the query to one file with the first title, and all of the
> records for all of the OfficeID's. I cannot seem to file the query
> satisfactorily however.
>
> No matter what I do I get some variation of the message:
>
> "...could not find the object 'qryListOfCopiers_Xerox_Survey where
> [OfficeID]= 2' (Two being the correct value for the first set of
> records.)
>
> I have tried many different ways to do this (leaving a couple commented
> out in the code) and each one gives a very close variation of the same
> message.
>
> I am hoping that you will the optimum way to do this!
>
> Thank you,
>
> Patty
>
> Code:
>
>
>
>
> Set dbs = CurrentDB
>
> Set rst = CurrentDB.OpenRecordset("Select *
> from[qryCopiersTitles_Xerox_Survey]")
>
> strId = rst!OfficeID
>
> rst.Filter = "OfficeID = '" & strId & "'"
>
> Set rstFiltered = rst
>
>
>
> strTitle = rstFiltered!Title & "_" & ".xls"
>
> strSQL = "qryListOfCopiers_Xerox_Survey"
>
> 'strSQL = "qryListOfCopiers_Xerox_Survey where
> (qryListOfCopiers_Xerox_Survey.OfficeID) = " & rstFiltered!OfficeID &
> ")"
>
> 'strSQL = "qryListOfCopiers_Xerox_Survey where [OfficeID] = " & strId & ")"
>
>
>
> outputFileName = "C:\CopierSurveyForms\" & strTitle
>
> DoCmd.OutputTo acQuery, strSQL, "MicrosoftExcel(*.xls)",
> outputFileName, False, ""
>
> MsgBox "Export completed."
>
> rst.Close
>
> Exit Sub
>
>
>
>
>
>
> --
> Patricia Mapes
>
>
>

------------------------------------

------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/