Assumption: query results are all records from same [territory]
If so you might try:
Dim db As Database
Dim qdf As DAO.QueryDef
Dim rs As Recordset
Dim strTerritory As String
Set qdf = db.QueryDefs("QueryName")
Set rs = qdf.OpenRecordset()
'assigns value of the [territory] field in the first record to a string
strTerritory = rs![territory]
DoCmd.OutputTo ...... "c:\data\Report Output\Data for Territory "& strTerritory & ".xls"
Note that if the query is a parameter query you'll need to supply the parameters in the procedure:
qdf.Parameters("[Forms]![FormName]![FieldName]") = Me![FieldName]
(this example assumes the parameter is a value in a field on the current form)
If the query result includes records from more than one territory, obviously the string strTerritory will be the first territory returned.
Originally posted by Backle
I'm trying to use the output to function in a macro to export the results of a query into an excel spreadsheet. (Access 2000)
Is there any way to get Access to generate the output file name for me based on some of the results of the query?
I have a field called [territory] in the query.
I tried something like this but it did not work:
I am having a similar problem with the outout to function in a macro. I am trying to ouput a report without having to enter the name and location for each one as there are over 30,000 files that will be created. I think I understand what the code is saying but I am unsure as to how to ncorporate the code into the database. Do I need to convert my macro to a module? If so, can I still call the module from the form? The filename string I am trying to create looks something like this: