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


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.

MS Access technical tips

Visual FoxPro technical tips

General Tips


More tips from Alvechurch Data

Frequent, automatic backups

How to create backups automatically in FoxPro or Access using the Windows Scripting Host

Read More

Backing up an Access Database

How to backup an Access database using VBA and DAO

Read More

Splitting an Access database

Splitting Access code and data into frontend and backend databases

Read More

Linking a table in Access

Connecting external tables into Access from another database

Read More

Migrating to Access 2002

Migrating to Access 2002 from earlier versions of the database

Read More