Using current month and year in a module for file name

[Wayne Cramer]

I have a vendor report card application which creates an individual RTF file for each of 245 vendors through a module with an array. The files are named as shown below. for example a file name might be April 2008 - ford motor company.rtf .

DoCmd.OpenReport "Report Card YTD", acViewPreview, , strSQL
strFileName = "April 2008 - " & cVendorName & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Card YTD", acFormatRTF, "H:\SUPPLY CHAIN\\RTF Report Cards\" & strFileName, False
DoCmd.Close acReport, "Report Card YTD"

Each month the module needs to be modified to reflect the next month and year. Is there a way for Access to automatically determine the current month and year and insert them in the file name?

 

[DJkarl]

Try Format(Date,"MMMM") for the month name and Year(Date) for the year.

 

[Wayne Cramer]

How would that code look replacing april 2008 in the example above?

 

[DJkarl]

How would that code look replacing april 2008 in the example above?

This

strFileName = "April 2008 - " & cVendorName & ".rtf"

Would become

strFileName = Format(Date,"MMMM") & " " & Year(Date) & " - " & cVendorName & ".rtf"

 

[Wayne Cramer]

This worked perfectly... except when I looked at the final files I realized that I need to have the month show the previous month name. For example the report run in May needs to have file names such as April 2008 - ford motor company.rtf . Sorry I wasn't clear on this.

 

[DJkarl]

For last month try

Format((Month(Date)-1) & "/1","MMMM")

 

[Wayne Cramer]

It works perfectly! Thank you!