In certain situations, you may need to query your data by the last date modified. Mary Ann Richardson shares a simple trick for adding a field that logs the date and time a record is changed, making such queries a snap.
Do you need to query your data by the last date modified? For example, say you would like to include donors whose records show no activity during the last six months in a special fund-raising appeal. How do you know which donors are to be included? Follow these steps:
- Add a field to your Donors table called Date Modified and assign it a Date/Time data type. Then, open the form used to update the table in Design view.
- Open the form's property sheet.
- Click in the Before Update property box in the Event tab.
- Click the Build button and select Code.
- At the prompt, enter the following code:
- Press Alt + Q.
Now each time a user changes a record, Access will enter the date and time from the system before the changes are updated. When the record is accessed again, the Date Modified field will contain the date and time of the last modification. You can query that field to determine which records have not been updated within the last six months.