5 Examples of Microsoft Access Date Functions

Microsoft Access date functions are a powerful way to retrieve and analyze data in your Access databases, but very few users know about these functions or how to use them. In this article, I will describe a few ways you can leverage Microsoft Access date functions in Access 2019 and earlier versions.

From automating dates to verification to search, here are five date functions in Access that you need to know.

If you find yourself constantly entering whatever today’s date is in your Access forms or tables, you are wasting valuable time. Access can do that for you!

The simplest way to insert today’s day is to add a simple Date() function to the Default Value property of your table. Let’s say we have a database that tracks applicants and case workers, and when cases are assigned, signed out, and closed. Following this example, let’s say we would like to automate the date for when a case is received.

1. Locate the table containing the date field you wish to modify and open it to Design View (right-click the table and choose Design

2. Once in Design View, select the field you wish to modify. In our example, it will be the DateReceived field.

3. In the Field Properties area, on the General Tab, locate the property for “Default Value” and type Date().

4. Switch to your table view and verify that for new records, today’s date is automatically populated in the field.

Once this property is set for your field, users can skip past this field when doing data entry. Or, users can modify the date as necessary. If the field is time-sensitive as well, you can also use the Now() function in place of the Date() function to store both the date and time information.

Date functions are also useful for controlling what dates are allowed to be entered into a given Date/Time field. Let’s say we had a sales table where we only want users to enter a transaction occurring on or before today’s date. We can achieve this level of control by writing an expression in the field’s Validation Rule property box inside the table.

1. Open the table containing the field you want to control to its Design View.

2. Select the field you wish to change. In our example, the field is our “Invoice Date” field. 

3. In the bottom portion of the screen, under Table Properties, click inside the Validation Rule property box and enter <=Now(). This will ensure that users enter a date occurring on or before today’s date. In other words, the date entered must be less than or equal to now.

4. Type “Transaction must occur on or before today’s date” inside the Validation Text property box. That way, if a user enters a date occurring after whatever “now” is, the user will be alerted that the “Transaction must occur on or before today’s date.”

If the date entered is valid, the user will not receive any warnings and the record will be saved.

• >=Date(): Disallow date values in the past.

• [End Date]>=[Start Date]: Ensure the end date value comes after the start date.

• [RequiredDate]<=[OrderDate]+30: Requires an entered date to occur no more than 30 days after the order date.

• >=#01/01/2019#: Requires a date entered on or after January 1, 2019.

In addition to using date functions for controlling and streamlining data entry, Microsoft Access date functions really shine when used as query criteria to filter or limit query results by date or date range. 

In this example, we will build a query to filter results and show only records that haven’t been updated within the last six months.

1. Create a query using the Access Query Designer (Create tab > click Query Design). This will open a Show Table dialog box.

2. Select the table you wish to query. Click Add and then close the dialog box.

3. Add fields to your query by double-clicking or dragging the desired field down to your QBE grid. In our example, we want to display the First Name, Last Name, E-mail Address, and LastUpdated fields.

4. After all the fields have been added, we will want to add an expression to show records last updated over 6 months ago (this is done underneath the LastUpdated field in the first criteria box).

To accomplish this, we can use the DateDiff function to calculate the difference, in months, between the LastUpdated date and whatever now is. Then it will show only those records that are over 6 months old.

So, our expression would be DateDiff(“m”,[LastUpdated],Now())>6.

To explain what you are seeing, the DateDiff function has three required function arguments: interval, date1, and date2. Here, the interval is “m” because we want to calculate in total months elapsed in order to find records older than 6 months. Date1 and Date2 represent the two dates for which we want to calculate the difference. 

In our example, Date1 is the information stored in the LastUpdated field. For Date2, since we want to save and reuse the query, we want to reference the Now() function to calculate using the system date and time. So, our DateDiff function is simply calculating the difference between when the field was last updated and right now. To limit our query results down to show only those records older than 6 months, we can then tack on a > symbol and the number 6 after the DateDiff function.

Upon saving and rerunning this query, you might notice that Access will display our written expression a bit differently, but it is just another way of expressing the same thing.

In addition to locating records by a difference between two dates, you can also query your database based on part of a date. If we want to locate contacts whose birthday is today, that is exactly what we will need to do—match part of the date (month and day) with today’s month and day.

1. Use the DatePart function DatePart(<<interval>>, <<date>>, <<firstdayofweek>>, firstweekofyear>>). Interval is a required string expression representing the interval of time you want returned. Date is another required variant representing the value or date you want to evaluate. The last two arguments are optional, and are not necessary for our example.

2. An expression to extract the month from a date field would look like DatePart(“m”,[Birthday]). “M” stands for month. [Birthday] is the name of the field that stores your contact’s birthdays. To extract the day only, replace the “m” with “d”. 

Putting everything together, we’d need to write an expression that sets those extracted values equal to today’s month and day. For that, we could use the same DatePart function to extract the day and month, and change the Date argument to the Now() function. So, the complete query criteria would look like this:

A Data Macro will automatically update a LastUpdated field to the current date and time whenever a record is modified.

For this example, let’s say we want to track when an employee’s contact information was last updated. We will build a query to locate employees whose information has not been updated recently (say, in the last year).

1. Open the employee’s table (or any table you wish to track when records were last modified) to Design View and add a new Date/Time field named “LastUpdated”. This field will be used to store the date/time information gathered whenever a record is updated.

2. Save the table.

3. In the Table Tools | Design tab, click the Create Data Macros button and select Before Change. This will open up the Macro Designer for the Before Change property, meaning any actions you program here will be evaluated and applied before a record is saved to the database.

Since we want to log when a record was last updated, we want to apply our timestamp or date function to the LastUpdated field just before the record is saved.

4. Click the Add New Action drop menu and select the action SetField from the list. That will add the action, plus two required arguments for the Action, Name, and Value.

5. In the Name box, write the reference to the field you would like updated before the change of the record. In our example, we would type “LastUpdated”.

6. In the Value box, write an expression for how you’d like the field changed. In our example, we’ll need to type Now().

7. Click Save in the Macro Tools | Design tab and then click Close. The Macro should now be applied to our table.

8. Save the table and test the data macro by locating a record in the table and editing it. Upon moving off the record, Access should not only save changes to the record, but also enter the current date and time into the LastUpdated field.

Over time, as more data is edited and entered, you can query this field to check and see which employees’ information might be outdated, and request that those employees check and update their information as necessary.

If you want to learn more about Access, Pluralsight has the resources and courses you need to gain an edge in your career. Scroll to the bottom of this page for a trial offer that includes 10 free days! 

Heather Ackmann is an accomplished instructor who has taught over 4,000 students at the high school, college, and adult levels. Specializing in computer applications, writing, and literature, she holds a degree in English and Secondary Education, an Illinois Type 09 Initial Teaching Certificate, and is a Microsoft Certified Master for Office 2003. While her energetic and easy-to-follow style is ideal for beginners to computer applications, her knowledge, thoroughness, and foresight in potential problems will satisfy the more proficient user.