How to Add CreatedDate and ModifiedDate Fields in Microsoft Access
Question details
The user wants to know if Microsoft Access automatically includes default fields for tracking creation and modification dates, and how to implement them.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Tracking record changes and maintaining history for database table updates.
- Observed behavior
- Access tables do not automatically include CreatedDate or ModifiedDate columns by default, requiring a manual setup.
Ensure you have Design View permissions for your Microsoft Access database tables and basic familiarity with Access data macros.
Add Fields Manually and Configure Data Macros
Create custom date fields and set up AfterInsert and AfterUpdate macros to automatically log when records are created or changed.
Since Microsoft Access does not provide built-in fields for tracking record creation or modification, you must manually add these columns to your table. Once added, Access Data Macros act as triggers that automatically stamp the current date and time whenever a user adds or edits a record.
Open your Access table in Design View. Add two new fields named 'CreatedDate' and 'ModifiedDate', and set their Data Type to 'Date/Time'.
Select the 'CreatedDate' field. In the Field Properties pane at the bottom, enter '=Now()' in the Default Value box to automatically stamp the current time when a new record is created.
Save the table and switch to Datasheet View. Navigate to the 'Table' tab on the Access Ribbon, and click on 'Before Change' in the Named Macros or Events group.
In the macro designer, add an 'If' block to check if the record is being updated. Use the 'SetField' action to update the 'ModifiedDate' field to '=Now()'. Save and close the macro.
Looking for a Free Alternative to Microsoft Office?
While Microsoft Access is used for complex databases, WPS Office offers a free, lightweight, and highly compatible suite for all your document, spreadsheet, and presentation needs. Manage your data effortlessly with WPS Spreadsheet without the high subscription costs.
- 1. Download the Installer: Visit the official WPS website and download the free WPS Office installation package.
- 2. Install WPS Office: Run the installer, follow the on-screen prompts, and select your preferred language settings.
- 3. Open Your Files: Launch WPS Office and instantly open your existing Excel, Word, or PowerPoint files with perfect formatting.
Frequently Asked Questions
Can I set a default value for the CreatedDate field without using macros?
Yes. In the table's Design View, you can select the CreatedDate field and set its 'Default Value' property to =Now(). This will automatically stamp the current date and time when a new record is created, bypassing the need for an AfterInsert macro.
Why isn't the ModifiedDate updating when I change a record?
A Default Value property only applies when a record is initially created. To update the ModifiedDate field upon subsequent changes, you must implement a Data Macro (such as Before Change or After Update) to explicitly overwrite the field's value with the current time.
Is there a way to track who modified the record in Access?
Yes. Alongside your date fields, you can add a 'ModifiedBy' Short Text field. You can then use Data Macros or VBA code to capture the current user's system login name or application username and save it to this field whenever a record is modified.
