DateAdd Function

Returns a Variant (Date) containing a date to which a specified time interval has been added.

Syntax

DateAdd(interval, number, date)

The DateAdd function syntax has these arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Argument Description
interval Required. String expression (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.) that is the interval of time you want to add.
number Required. Numeric expression (numeric expression: Any expression that evaluates to a number. The expression can be any combination of variables, constants, functions, and operators.) that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
date Required. Variant (Date) or literal representing date to which the interval is added.

 Tip    Try Office 2010 In Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.
Watch a video or try Office 2010.

Settings

The interval argument (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

Remarks

You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now.

To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").

The DateAdd function will not return an invalid date. The following example adds one month to January 31:

DateAdd("m", 1, "31-Jan-95")

      

In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is 31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.

If the calculated date would precede the year 100 (that is, you subtract more years than are in date), an error occurs.

If number isn't a Long (Long data type: A fundamental data type that holds large integers. A Long variable is stored as a 32-bit number ranging in value from -2,147,483,648 to 2,147,483,647.) value, it is rounded to the nearest whole number before being evaluated.

 Note    The format of the return value for DateAdd is determined by Control Panel settings, not by the format that is passed in the date argument.

 Note    For date, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri. If month values are names, the name must be consistent with the current Calendar property setting. To minimize the possibility of month names conflicting with the current Calendar property setting, enter numeric month values (Short Date format).

Example

 Note   Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.

This example takes a date as input and uses the DateAdd function to display a corresponding date a specified number of months in the future.

Dim FirstDate As Date    ' Declare variables.
Dim IntervalType As String
Dim Number As Integer
Dim Msg

IntervalType = "m"    ' "m" specifies months as interval.
FirstDate = InputBox("Enter a date")
Number = InputBox("Enter number of months to add")
Msg = "New date: " & _
    DateAdd(IntervalType, Number, FirstDate)
MsgBox Msg
 
 
Applies to:
Access 2007