Adding Date to Exported File

[Danu]

I am using DoCmd.TransferSpreadsheet to move a file into Excel from Access.
This will be done daily so the date must be associated with the file name.
Each time I compile the module, I get a CONSTANT EXPRESSION REQUIRED error.

This is the output line:

Const OUTPUT_PATH = "P:\Reports\Daily File for " & Format(Date, "Long Date")
& ".xls".

FORMAT is highlighted after compiling. I'm new to this so it is probably
something obvious that I am doing wrong.

Please help and thank you.

 

[Jeanette Cunningham]

Danu,
this message is Access trying to tell you not to use a constants for the
output path.
Because the Date changes every day at least, it can't be a constant.
Change the path from a constant to a string variable.

Dim strOutpathPath as String
strOutpathPath = "P:\Reports\Daily File for " & Format(Date, "Long Date")
& ".xls".

Jeanette Cunningham

 

[Danu]

Thank you, Jeannette. That worked!
Can you suggest a beginner's "manual" to learn this facet of Access?

 

[Jeanette Cunningham]

Danu,
I'm not sure which facet of access you mean.
It's hard to recommend books, as different people have different ways of
learning.
Personally I learned a great deal from 2 books by John L Viescas called
Microsoft Office Access 2003 and Building Access Applications.
Both these books have several sample databases of many different types with
code that is well very well explained. I learned a lot from seeing how these
samples used code.

Jeanette Cunningham

 

[Danu]

Once again, thanks. I'll take a look at these books. It is someplace to start.

-d