Export to Excel with Parameter in Filename

[clk]

Hi. I have the following code. What happens is I have a criteria
parameter on the query prompting the user for a date. The parameter
is [Enter Start Date]. What I would like is that parameter included
in the filename when it exports to Excel. Can this be done? It fails
on the line tDate = Me.[Enter Start Date]. Any suggestions are
appreciated.

stDocName = "qryTesting"
FileName = "OrdersEntered"
tDate = Me.[Enter Start Date]
FileDir = "q:\receptionist\daily reports\"
XFile = FileDir & FileName & tDate & " .xls"

DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "qryTesting", _
acFormatXLS, XFile, True
DoCmd.Close acQuery, "qryTesting", acSaveNo

 

[John Spencer]

Me refers to a form or a report, it cannot refer to a query.

If possible, I would suggest that you use a control on a form to enter the
date. The form must be open for the query to get the value.

In the query replace [Enter Start Date] with
Forms![Name of Form]![Name Of Control]

Then
tDate = Format(Forms![Name of Form]![Name Of Control],"mm-dd-yyyy")

You must format the date to something that does not use the / marks or other
unacceptable characters for a file name. Some choices are mm-dd-yyyy or
yyyymmdd or mm-dd-yyyy or mdyy

If tDate is defined as a datetime variable then you would need to apply the
format when you set the value of XFile.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County