Re: [MS_AccessPros] strSQL issues with filtering

Patty-

Steve is correct. Ignore my reply.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)




On Nov 25, 2014, at 5:42 PM, Steve Conklin StephenMConklin@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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







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

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


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

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/