Re: [MS_AccessPros] strSQL issues with filtering

 

Patricia-

Try using:

strSQL = "SELECT * FROM qryListOfCopiers_Xerox_Survey where [OfficeID] = " & strId & ")"

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 4:52 PM, Patricia Mapes patty.mapes@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___