Format date-time so it can be used as filename

[DaveMac]

And today is beginning like yesterday. Help with format date. As you can see I want a file saved at 8 am on Jan 1st 2011 to be strfiledate “0800 1-1-2011”. I just need a date/time down to the minute, seconds if it is easy, so I can ensure unique filenames.



'*****************************************************************************************
'Create the file when cmd_csv_button is clicked on the main form
'
'*****************************************************************************************
Private Sub cmd_csv_button_Click()
'Globals
Dim strfilepath As Variant
Dim strfiledate As Date
Dim strfilename As Variant
' Location where the file will be saved
strfilepath = "c:\"
'Place the date-time in the filename so files are not overwritten
'need to keep history per rec 7903 from manugistics learnings regroup 11-04-2010 at CHI in rm 608
strfiledate = Replace(Format(Now, "mmm/dd/yyyy"), "/", "-")
'File name with file date-time and the file type
strfilename = "Apollo_Load" & strfiledate & ".CSV"

'DoCmd.TransferText acExportDelim, , "qry_Apollo_load_csv_maker", strfilename
MsgBox "The file was saved successfully" & strfilename

End Sub

 

[MajP]

Format(Now, "HHNNSS YYYY-MM-DD")

 

[DaveMac]

sweet!
BTW I had to make the variable a variant not date. not sure why date did not like time?

 

[MajP]

Because format function returns a string. It is no longer a date and cannot be treated like a date unless you convert it back.