Visit our new web pages

We have launched a new extended set of web pages at www.alvechurchdata.co.uk. These old pages will stay on the server because people still have links to them but they will not be updated.

The new version of this page is at www.alvechurchdata.co.uk/hints-and-tips/accdatestamp.html.

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 a file name from the date
' Accepts...........: Nothing
' Returns...........: A file name of the pattern 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.

The Site Search feature requires JavaScript. If your browser does not support Javascript then please use our Site Map instead.

Hints & tips

The textbox class in Visual FoxPro 9 has a new Autocomplete property which shows the user the previous values that have been entered in that textbox.
Autocomplete in VFP 9

Your Access database will look more impressive if you add custom toolbars...
Custom toolbars

FoxPro has always had functions to read and write files at a low level...
Foxpro low level file functions

More...
More pages of hints and tips for users of Microsoft FoxPro and Access databases.

Site Map