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:
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.
Advertisements