5 Examples of Microsoft Access Date Functions
- select the contributor at the end of the page -
If you have never used Microsoft Access date functions, take a look at this quick Beginner's Guide to Microsoft Access Built-in Date Functions that I recently created and shared on our Office YouTube channel.
1. Automatically Insert Today's Date (Beginner Level)
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, and you don't have to be an Access expert to do so!
There are a few ways to have Access insert today's date, but the simplest way is 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.
- Locate the table containing the date field you wish to modify and open it to Design View (right-click the table and choose Design View). In our example, we would need to open the table that stores our case information.
- Once in Design View, select the field you wish to modify. In our example, it will be the DateReceived Field.
- In the Field Properties area, on the General Tab, locate the property for "Default Value" and type Date().
- Next, switch over 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 modify the date as necessary. Alternatively, 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.
2. Prevent Data Entry for Dates After Today's Date (Intermediate)
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.
- Open the table containing the field you want to control to its Design View (right-click table, and click Design View).
- Select the field you wish to change. In our example, the field is our "Invoice Date" field.
- In the bottom portion of the screen, under Table Properties, click inside the Validation Rule property box, and enter the following:<=Now()This will ensure that users enter a date occurring on or before today's date. Or, in other words, the date entered must be less than or equal to now.
- Next, 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 date "must occur on or before today's date."Note: This message will only appear if the data entered does not meet the validation requirements as written in your expression. If the date entered is valid (does occur on or before today's date), the record will simply be saved and the user will not receive any warnings.
For more examples of validation rules and expressions, please visit the Microsoft Help Topic Create a validation rule to validate data in a field.
3. Store the Date a Record was Last Modified or Updated (Advanced)
The following method is only available for Microsoft Access 2010 databases, as this will use a new feature called a Data Macro to 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 and build a query to locate employees whose information has not been updated recently (say, in the last year).
First, I will need to add a field to my table and name the field "LastUpdated." This field will be used to store the date/time information gathered whenever a record is updated (you can use Microsoft's Northwind sample database to practice, if you like).
- Open the employee's table to Design View (or any table that you wish to track when records were last modified) and add a new Date/Time field named "LastUpdated."
- Save the table.
- In the Table Tools | Design tab, click on "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. And 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.
- Click in 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.
- 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."
- In the Value box, write an expression for how you'd like the field changed. In our example, we'll need to type Now().
- Click Save in the Macro Tools | Design tab, and then click Close.The Macro should now be applied to our table.
- Save the table.
To test the data macro, just locate a record in the table and edit something. Upon moving off the record, Access should not only save changes to the record but enter the current date and time into the LastUpdated field.
Over time, as more data is edited and entered, I can query this field to check to see which employees' information might be outdated, and request that those employees check and update their information as necessary.
4. Filter by Date Functions (Intermediate)
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 the previous example, we created a field to track when employee records were last updated. Now, we can build a query to filter results to show only records that haven't been updated within the last six months.
- Create a query using the Access Query Designer (Create Tab > click Query Design).That will open a new query to Design View and open a Show Table dialog box.
- Next, select the table you wish to query, click Add, and then close the Show Table dialog box.
- You can then 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.
- After all fields have been added, underneath the LastUpdated field, in the first criteria box, we will want to add an expression to show records last updated over 6 months ago. To accomplish this, we can use the DateDiff function to calculate the difference, in months, between the LastUpdated date and whatever now is, and then show only those records that are over 6 months old. So, our expression would look something like this: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 old. 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, however, 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 greater than symbol and the number six after the DateDiff function.
Upon saving and rerunning this query, you might notice that Access will display our written expression a bit differently:
This is yet another way of expressing the same thing.
5. Locate Today's Birthdays (Intermediate)
In addition to locating records by a difference between two dates, you can also query your database based on part of a date. And if we want to locate contacts or employees 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. There are several ways to accomplish this in Access using date functions.
First, we could use the DatePart function to extract both the month and day from a field that stores employees' birthdays. The DatePart function looks like this:
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.
So an expression to extract the month from a date field would look something like this:
“m” stand for month
[Birthday] is the name of the field that stores employees' birthdates.
To extract the day, the only thing we have to change about the expression is the time interval.
Now, putting everything together, we'd need to write an expression that sets those extracted values equal to whatever today's month and day are. 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:
In other words, the month of Birthday is equal to the month of right now, and the day of Birthday is equal to the day of now.
This is just one way you could write this expression. Another way is to use the Month and Day functions instead of the DatePart function. Here is a video showing how to do that.
I hope this will get you to a good start with Microsoft Access Date Functions.