Creating a datestamp in Access

A file name based on the date it was created can be very useful for log files or backups. This VBA function will generate an eight-character filename from the year, month and day of the current date.

Public Function MakeFileName()
'Description.......: Makes file name from the date
'Accepts...........: Nothing
'Returns...........: A string like 20051225
'Major change list.:


Dim strDay As String
Dim strMonth As String
Dim strYear As String

'-- Generate a yyyymmdd date string
strYear = CStr(Year(Date))

'-- Add leading zeroes if needed for month and day
strMonth = CStr(Month(Date))
If Len (strMonth) = 1 Then
   strMonth = "0" & strMonth
End If

strDay = CStr(Day(Date))
If Len (strDay) = 1 Then Then
   strDay = "0" & strDay
End If

MakeFileName = strYear & strMonth & strDay

End Function

Notes

The function returns a string so it can be used directly whenever you want a name that can be easily recognised. For example to export the customer table into Excel;

DoCmd.TransferSpreadsheet acExport, 3, _
      acSpreadsheetTypeExcel3, 'Customers', _
      MakeFileName() & '.xls', True

If you ran this code on Chrstmas Day it would save the data to '20051225.xls'. The filename is laid out in year, month, date order so that you can easily sort a collection of filenames chronologically.

With a bit more work the function can be extended to include hours, minutes and seconds to give many unique names on the same day.

This function was originally written in VBA in Access 97 but can be easily modified to run as in VBScript.