how to append field date value to output filenam

[cirugio]

Hoping someone can help. I currently have an access database for which I have created some reports against. I want to output the report also to an excel file. I am able to do this with no problem using Visual Basic ACCESS DoCmd.OutputTo acOutputReport command (see below). This code is stored in a VB Module. The only thing I can't figure out is how to append the asofdate field to the file name.

The asofdate field is currently on every record of my Sales access table and will always be the same for all records each day. How can I append the asofdate field to the filename when the command below is executed to save the report into an excel file?


This is my current code:

DoCmd.OutputTo acOutputReport, "Report 01 - TodaysSales", acFormatXLS, "c:\TodaysSales.xls", False


Current Output is --> Report 01 - TodaysSales.xls
Would like outfile to be this instead -->Report 01 - TodaysSales as of mm/dd/yy.xls

** where mm/dd/yy is the value from the asof_date field in the access table

 

[pbaldy]

However you get the date:

DoCmd.OutputTo acOutputReport, "Report 01 - TodaysSales", acFormatXLS, "c:\TodaysSales" & Format(DateField, "mmddyy") & ".xls", False

You don't want the slashes in a file name.

 

[cirugio]

How to get the date into the file name is what I don't exactly now how to do. Was hoping for some guidance. The date to append to the file is stored in a table name "sales" and the field is called w_asofdate. This w_asofdate field is date/time in access. Just don't know how to code and append it to the filename.

Any help is appreciated. Thanks for your time in advance.

 

[pbaldy]

You can use a DLookup() to get it from the table. You don't need a criteria if all records have the same date.

 

[cirugio]

I never have used dlookup() how does that work exactly? Sorry, I am under the gun to get this done by end of day today. Thanks.

 

[pbaldy]

I would have expected you to look in help, particularly if you're in a hurry.

DLookup("FieldName", "TableName")

 

[cirugio]

My apologies. I did look it up before I got a chance to delete my post to inform you. Thank you for all your help.

 

[pbaldy]

Happy to help. You got it working?