totn Access Functions

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples.

Description

The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

Syntax

The syntax for the DateDiff function in MS Access is:

DateDiff ( interval, date1, date2, [firstdayofweek], [firstweekofyear])

Parameters or Arguments

interval

The interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values.

Interval Explanation
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
date1 and date2
The two dates to calculate the difference between.
firstdayofweek

Optional. It is a constant that specifies the first day of the week. If this parameter is omitted, Access assumes that Sunday is the first day of the week. This parameter can be one of the following values:

Constant Value Explanation
vbUseSystem 0 Use the NLS API setting
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
firstweekofyear

Optional. It is a constant that specifies the first week of the year. If this parameter is omitted, Access assumes that the week containing Jan 1st is the first week of the year. This parameter can be one of the following values:

Constant Value Explanation
vbUseSystem 0 Use the NSL API setting
vbFirstJan1 1 Use the first week that includes Jan 1st (default)
vbFirstFourDays 2 Use the first week in the year that has at least 4 days
vbFirstFullWeek 3 Use the first full week of the year

Returns

The DateDiff function returns a numeric value.

Applies To

The DateDiff function can be used in the following versions of Microsoft Access:

  • Access 2019, Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000

Example

Let's look at how to use the DateDiff function in MS Access:

DateDiff ("yyyy", #15/10/1998#, #22/11/2003#)
Result: 5

DateDiff ("m", #15/10/2003#, #22/11/2003#)
Result: 1

DateDiff ("d", #15/10/2003#, #22/11/2003#)
Result: 38

Example in VBA Code

The DateDiff function can be used in VBA code in Microsoft Access.

For example:

Dim LValue As Integer

LValue = DateDiff ("d", #15/10/2003#, #22/11/2003#)

In this example, the variable called LValue would now contain the value of 38.

Example in SQL/Queries

You can also use the DateDiff function in a query in Microsoft Access.

For example:

Microsoft Access

In this query, we have used the DateDiff function as follows:

Expr1: DateDiff('d',[CategoryDate],Date())

and

Expr2: DateDiff('d',#15/10/2003#,#22/11/2003#)

The first DateDiff function will calculate the difference in days between the CategoryDate field and the current system time. The results will be displayed in a column called Expr1. You can replace Expr1 with a column name that is more meaningful.

For example:

Difference: DateDiff('d',[CategoryDate],Date())

The results would now be displayed in a column called Difference.

The second DateDiff function will calculate the difference in days between the two dates - 15/10/2003 and 22/11/2003. The results will be displayed in a column called Expr2.

Frequently Asked Questions

Question: I am very new to Access and would like to know how to write the expression for DateDiff to Calculate between a due date and today and to show the number as a + not a -. I keep getting a minus figure. The number is correct otherwise.

Answer:If you are getting a negative answer, you have two ways to return a positive value. First, you can switch the values in your DateDiff function so that the smaller date comes first. Or second, you can use the Abs function to return the absolute value of the result.

For example, if the following DateDiff function is return a negative value:

Expr1: DateDiff('d',[Due_Date],Date())

You could fix the DateDiff function with either of these solutions:

Solution #1 - Switch the order of the dates

Expr1: DateDiff('d',Date(),[Due_Date])

By switching the order of the dates so that the smaller date comes first, the DateDiff function will return a positive value.

Solution #2 - Use the Abs function

Expr2: Abs(DateDiff('d',[Due_Date],Date()))

By using the Abs function on the result of the DateDiff function, you will always get a positive value.

Share on: