How to Access Query Equivalents for DATEDIF

Access offers a function allowing you to calculate the difference between two dates.

Unlike its spreadsheet cousin Excel, Microsoft Access doesn't contain an intuitive formula to calculate the amount of time that's elapsed between dates. You can, however, use a simple formula in the query grid to determine the number of days, months or even years between two dates, or the number of seconds, minutes or hours between two times. This is an especially helpful function for companies that use the database program for tracking orders or shipments.

Instructions

1. Open your database. Click on the "Create" tab at the top of the screen, then select the "Query Design" icon in the upper-right corner.

2. Add the table with the dates to the query grid. Close the dialog box. Put the two date fields into the query grid by double-clicking or dragging and dropping them.

3. Click on the grid next to the dates and type in the following formula: =DateDiff(interval, field1, field2). The interval should be "d," "m" or "yyyy," for days, months or years. Other interval options include "q" for quarters, "y" for day of year, "w" for weekday, or "ww" for week of year. You can even use "h," "m" or "s" for hours, minutes or seconds.

4. Use the precise field name for each date field. If your date fields are named "Date1" and "Date2," the expression in the query grid should look like this: Expr1: DateDiff("d",[Date1],[Date2]). You can change the field name from "Expr1" to something more descriptive, such as "Shipping Time."

5. Click on the red exclamation point to run the query. You can also examine the query syntax by clicking on the "View" icon in the upper-left corner of the screen and selecting "SQL View."

References

• Tech on the Net: MS Access DateDiff Function
• Microsoft Office: DateDiff Function
• Microsoft Office: Calculate the difference between two date or time values