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

Minggu, 30 November 2014

Re: [MS_AccessPros] Re: strSQL issues with filtering

Patty-

What you need to do is modify the query on the fly. Try this:

Dim qd As DAO.QueryDef

' Create a temp query

Set qd = dbs.CreateQueryDef "zqryExcel"

Do Until rst.EOF

' Modify the query to filter on the current OfficeID

qd.SQL = "Select * from [qryListOfCopiers_Xerox_Survey] where [OfficeID] = " & (rst!OfficeID) & ";"

outputFileName = "C:\CopierSurveyForms\" & strTitle

' Output using the filtered temp query

DoCmd.OutputTo acOutputQuery, "zqryExcel", "MicrosoftExcel(*.xls)", outputFileName, False, ""

rst.MoveNext
Loop
rst.Close

' Close and delete the temp query

qd.Close

dbs.QueryDefs.Delete "zqryExcel"

What the above code is doing is creating a temporary query and then sets its SQL to filter on the current OfficeID. It uses the filtered query to output your data on each pass.

When done, it closes and deletes the "temp" query.

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

I tried Steve's qryTemp idea but couldn't make it work. I created qryTemp in the application, and put a reference to the strId but either it doesn't work, or I don't know how to do it.

What I am trying to do it take a list of 500+ records (in a query) and create more or less 60 xls files, where each file will only have one "OfficeID". The files will then be emailed to the 60+ offices.

I have one query with Titles for the xls file - more or less 60 records.

The second query has the records. I had thought I would be able to use the query and "where" to do this, but I'm not having success.

I also tried transfer spreadsheet and ran into the same issues.

I guess the most important question is, can I do this at all? It seems obvious, but I cannot figure it out.

I am grateful for any and all help.

Thank you,

Patty

This is my code:

Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstDetail As DAO.Recordset
Dim strTitle As String
Dim outputFileName As String
Dim qryTemp As String
Dim strOutput As String
Dim qryString As String
Dim frmListXeroxCopiers As Form

Set dbs = CurrentDB

Set rst = CurrentDB.OpenRecordset("Select * from[qryCopiersTitles_Xerox_Survey]")

Do Until rst.EOF

Set rstDetail = CurrentDB.OpenRecordset("Select * from[qryListOfCopiers_Xerox_Survey] where [OfficeID] = " & (rst!OfficeID) & ";")

MsgBox "title data complete"

strSQL = "qryListOfCopiers_Xerox_Survey"

outputFileName = "C:\CopierSurveyForms\" & strTitle

DoCmd.OutputTo acOutputQuery, strSQL, "MicrosoftExcel(*.xls)", outputFileName, False, ""

'DoCmd.OutputTo acOutputReport, qryListOfCopiers_Xerox_Survey, acFormatXLS, "", False, ""
rst.MoveNext
Loop
rst.Close

On Tue, Nov 25, 2014 at 10:52 AM, Patricia Mapes <patty.mapes@gmail.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