Hi...I have a question on exporting a query to Excel with a file name that contains a variable date found in the source query.
I have added and modified Dim statements a number of time, tried to add the field where the PayDt information is found, etc., however I keep receiving a variety of errors. Currently, my code looks like this:
Code:
Private Sub ctlFormUSA_Click()
DoCmd.SetWarnings False
DoCmd.OutputTo acOutputQuery, "qry_Form_USA", "ExcelWorkbook(*.xlsx)", "q:\2013Test\Admin\Forms\USA_FORM_" & Format(ProcDt, "ddmmmyyyy") & ".xlsx", False, "", , acExportQualityPrint
MsgBox "Creation of Form for ""USA"" has been completed.", vbInformation, "Export Complete"
DoCmd.SetWarnings True
The ProcDt value needs to be from a field in the "qry_Form_USA". That field in itself is a logic statement that evaluates data from two different tables and determines what the final result should be.
I would like the file name that is exported to be "USA_FORM_[Then the date from that field which needs to be in 17Jun2013 format]. This would allow us to change the "ProcDt" in the table that the query links to versus having to hard-code it and change it out each time in the code.
Any suggestions on how to make this work?
Thanks.