How to Subtract Two Dates on Access Query
- 1). Click "Queries" under the "Objects" heading on the left. Click "New" and then "Design View." Click "Close" on that window and then click "View" and "SQL View." Queries such as this with complex expressions are easier to type into the SQL window than to try and get to from a "Wizard."
- 2). Type in the following query to determine the difference in two date fields in the database:
Select DateDiff("period", [datefield1], [datefield2]) from tablename where selection criteria.
Substitute the period you want the difference shown in for the word "period" in the query. "d" is for days, "m" is for months, and "yyyy" is for years. Enclose the period in quotes.
Substitute the actual field names and table name. Substitute the actual selection criteria, such as "where orderid = 1234."
Either date field can be substituted with an actual date enclosed in two "#" characters.
Either date field can be substituted with the expression "now()" to compare the date field with today's date.
The following are sample queries:
Select DateDiff("d", [OrderDate], [SalesDate]) from Sales where Client = "Acme"
Select DateDiff("=m", [OrderDate], now()) from Sales where Client = "Acme"
Select DateDiff("yyyy", [OrderDate], #1/1/2000#) from Sales where Client = "Acme" - 3). Click the red exclamation point on the toolbar to test and run the query. Depending on your "Where" expression, you will get a single number of a column of numbers displaying the results of the query.
Source...