Newbie - Passing variable from function to query

iidx036

Registered User.
Local time
Today, 14:25
Joined
Sep 9, 2004
Messages
22
Hi,

I am exporting data from a table to Excel files (in a loop) - called location101.xls, location102.xls etc from a docmd.outputto in a function.

In this function I have a counter that increments in each loop that updates the file name.

What I would like to do is pass this counter number from my function to my query so it will select the location field as 1 then 2 then 3 and so on.... as my function loops.

Can anyone help????

TIA
 
I don't quite understand what you're trying to do but I can tell you that you can't modify a query on the fly. You can supply a parameter value with a function but you can't use a function to specify a column name or relational operator or any structural element. Parameters may only contain data values.
 
Pat,

Thanks for you reply. I guess my wording was a bit vague!

Here is the function:
--------------------------------------------------------------
Public Function process_location_spreadsheets()

Dim locationnum As String
Dim output_file As String
Dim extension As String

locationnum = 1
'Locationnum is what I want to add to my query to bring back the records for that location. Would the criteria be something like [process_location_spreadsheets].locationnum?

extension = ("C:\del_cl_output\location" & shopnum)
output_file = (file1 & ".xls")
MsgBox output_file

Do While locationnum < 150

DoCmd.OutputTo acOutputQuery, "deleted clearance by shop", acFormatXLS, output_file

locationnum = locationnum + 1
extension = ("C:\location_sheets_output\location" & locationnum)
output_file = (extension & ".xls")

Loop

End Function
-------------------------------------------------------------------------

Thanks again
 

Users who are viewing this thread

Back
Top Bottom