Solved: Add Date to Filepath

[CreganTur]

I'm using this VBA function to export the results of a Query to Excel:


'Export Global report to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryNoResultsGlobal", "C:\NoResultsGlobal", True


I want to adjust the Filepath so that the current date (in the dd-mm-yy format) will be added to the end. ie: "C:\NoResultsGlobal 03-25-08"

I tried concatenating the Filepath with the Date variant [ "C:\NoResultsGlobal" & Date]

I tried using a variable:


Dim cdte As Date

'Export Global report to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryNoResultsGlobal", "C:\NoResultsGlobal" & cdte, True


So far nothing's working.:dunno

[Trevor]

try instead of "C:\Noresultsglobal" & cdre, true
to

"C:\Noresultsglobal" & Date(dd,mm,yy)

I know if you use

"C:\NOResultsGlobal" & Date() ' gives me dd,mm,yy (depending on my date formate on OS

[CreganTur]

NinjaEdit: The reason for my error is due to the Regional Formatting of dates on my computer. For most Comps in the USA, dates are in the dd/mm/yyyy format. The error was due the the forward slashes being added to my filepath. Here's the fix:

"C:\NoResultsReports\NoResultsGlobal" & " " & Format(Now(), "m-d-yy")

This will change the date so it is seperated by a valid character. Today's date shows as: 3-25-08

So my file shows as: "NoResultsGlobal 3-25-08"

____________________


try instead of "C:\Noresultsglobal" & cdre, true
to

"C:\Noresultsglobal" & Date(dd,mm,yy)


When I do this I get an error message (see attached file).



I know if you use

"C:\NOResultsGlobal" & Date() ' gives me dd,mm,yy (depending on my date formate on OS


I tried using this as well, but when I move on to the next parameter, the "()" parentheses disappear and I'm left with ["C:\NOResultsGlobal" & Date]

[Trevor]

I just assumed you are in the US cragentur since I didn't see a country/state flag under your name

[CreganTur]

I just assumed you are in the US cragentur since I didn't see a country/state flag under your name

Oh, I am- North Carolina:thumb

I was just stating that I had to format the Date to use a hyphen as a seperator because forward slashes cause the filepath to be invalid.