TransferText with last month's Date in filename

[Tiffany]

I'm trying to export access tables to excel using the following code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Central
Western", _
"Central Western Liability " & m & Format(Date, " YY") &
".xls", True

I have defined m as:
m = MonthName(Month(Now()) - 1, True)
As I want to have the last month's date in the filename.
Eg if it's February, I want the filename to be Central Western Liability Jan
09. I realise it might get a bit tricky when it's January and I want Dec 08,
as it's a different year.

But when I run it, I get the error message: Invalid procedure call or
argument pointing to the line where I have defined m.

Pls help

 

[John W. Vinson]

As I want to have the last month's date in the filename.
Eg if it's February, I want the filename to be Central Western Liability Jan
09. I realise it might get a bit tricky when it's January and I want Dec 08,
as it's a different year.

But when I run it, I get the error message: Invalid procedure call or
argument pointing to the line where I have defined m.

You can use DateAdd() do get this:

"Central Western Liability " & Format(DateAdd("m", -1, Date()), "MMM YY")
& ".xls", True

This will subtract one month from today's date (getting 12/12/08 if run
today); format this into a text string "DEC 08"; and concatenate it to your
boilerplate text strings.

 

[Tiffany]

Thank you so much, this was exactly what I was after, much appreciated.