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.

Creating a datestamp in Access

Notes

Frequent, automatic backups

Backing up an Access Database

Splitting an Access database

Linking a table in Access

Migrating to Access 2002

February 2012

Database development and training for Birmingham and the midlands

Specialists in Microsoft Access and Visual Foxpro database training and development

Phone us on 0121 445 1300, email sales@alvechurchdata.co.uk , or use our contact page