Banking

Timestamp new Access records

A simple Access field property can add the date and time for each new record created.

Auditing records is as complex or simple as the internal process requires. However, most auditing processes have at least one thing in common — they all want to know when each record was created. Fortunately, Access can automate this requirement without a bit of VBA code. If you need to add a Date Created field to a table, do so as follows:

  1. Open the table in Design view.
  2. Insert a new record row and give the field a meaningful name, such as DateCreated.
  3. Click the Data Type column's drop-down arrow and choose Date/Time.
  4. With that record row still selected, find the Default Value property field and enter the Now() function. This will include both the date and time. If you prefer just the date, use the Date() function.
  5. Save the table and close it.

When you add a record, Access will automatically insert the date (and time). However, as is, a user could overwrite the default value. If using this date for auditing purposes, consider limiting access to this field so users can't change the timestamp. In fact, you don't need to display this value in forms and reports, other than those used specifically for auditing purposes. Those entering data don't need to know that Access is timestamping the record.

Setting a default value is best done when you create the table because this property works only with new records. If the table contains preexisting data, you must manually update those records. In this case, you might not know the appropriate dates, so be sure to accommodate null values in your queries and functions if you leave this field blank for preexisting records.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks