Returns a Variant (Date) that contains a date with a specified time interval added.
Syntax
DateAdd(interval, number, date)
The DateAdd function has these arguments:
| Argument | Description |
|---|---|
interval |
Required. String expression that is the interval of time that you want to add. |
number |
Required. Numeric expression that is the number of intervals that 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 that represents the date to which the interval is added. |
Settings
The interval argument 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 won't 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, which means you subtract more years than are in date, an error occurs.
If number isn't a Long value, Access rounds it to the nearest whole number before it evaluates the expression.
Note
The format of the return value for DateAdd is determined by Control Panel settings, not by the format 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 match the current Calendar property setting. To reduce the chance of conflicts, enter numeric month values in Short Date format.
Query examples
| Expression | Results |
|---|---|
SELECT DateAdd("YYYY",1,[DateofSale]) AS Expr1 FROM ProductSales; |
Adds 1 year to the date values in the DateofSale field. |
SELECT DateAdd("YYYY",-1,[DateofSale]) AS Expr1 FROM ProductSales; |
Subtracts 1 year from the date values in the DateofSale field. |
SELECT DateAdd("d",10,[DateofSale]) AS NewDate FROM ProductSales; |
Adds 10 days to the date values in the DateofSale field and returns the results in the NewDate column. |
SELECT DateAdd("ww",-1,[DateofSale]) AS NewDate FROM ProductSales; |
Subtracts 1 week, or 7 days, from the date values in the DateofSale field and returns the results in the NewDate column. |
VBA 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, see Access VBA reference.
This example takes a date as input and uses the DateAdd function to display the matching 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