totn Access Functions

MS Access: DateAdd Function

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

Description

The Microsoft Access DateAdd function returns a date after which a certain time/date interval has been added.

Syntax

The syntax for the DateAdd function in MS Access is:

DateAdd ( interval, number, date )

Parameters or Arguments

interval

The time/date interval that you wish to add. It can be one of the following values:

Value Explanation
yyyy Year
q Quarter
m Month
y Day of the year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
number
The number of intervals that you wish to add.
date
The date to which the interval should be added.

Returns

The DateAdd function returns a date value.

Applies To

The DateAdd 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 DateAdd function in MS Access:

DateAdd ("yyyy", 3, #22/11/2003#)
Result: '22/11/2006'

DateAdd ("q", 2, #22/11/2003#)
Result: '22/05/2004'

DateAdd ("m", 5, #22/11/2003#)
Result: '22/04/2004'

DateAdd ("n", 51, #22/11/2003 10:31:58 AM#)
Result: '22/11/2003 11:22:58 AM'

DateAdd("yyyy", -1, #22/11/2003#)
Result: '22/11/2002'

Example in VBA Code

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

For example:

Dim LDate As Date

LDate = DateAdd ("s", 53, #22/11/2003 10:31:58 AM#)

In this example, the variable called LDate would now contain the value of '22/11/2003 10:32:51 AM'.

Example in SQL/Queries

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

For example:

Microsoft Access

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

Expr1: DateAdd('m',51,#22/11/2003#)

and

Expr2: DateAdd('h',2,[CategoryDate])

The first DateAdd function will add 51 months to the date 22/11/2003 and display the results in a column called Expr1. You can replace Expr1 with a column name that is more meaningful.

For example:

NewDate: DateAdd('m',51,#22/11/2003#)

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

The second DateAdd function will add 2 hours to the CategoryDate field and return the results in a column called Expr2.