ACCESS FEED: Re: [MS_AccessPros] strSQL issues with filtering

Selasa, 25 November 2014

Re: [MS_AccessPros] strSQL issues with filtering

Thank you Steve!
Seems brutally obvious now.. The acQuery bit and all!

Sent from my mobile device

> On Nov 25, 2014, at 12:02 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
>
> 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
>
>
>