Follow us:
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.

Applies To

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

  • 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.

Share: