databasedev.co.uk - database solutions and downloads for microsoft access

Calculating future Dates using the DateAdd function

The DATEADD function performs time and date calculations for matching properties having date types. Use the DATEADD function to obtain dates and times in a specified amount of time before the present. The following example shows the DATEADD function:

DateAdd(interval, number, date)

The DateAdd function syntax has these named arguments:

Part	Description
Interval	Required. String expression that is the interval of time you want to add.
Number	Required. Numeric expression 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.

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

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.

Let's look at the following example:

Suppose we sell electrical products, and with a product we can sell Manufacturers Guarantees. Now we will record the GuaranteeStartDate in the OrderDetails table, and also record the Guarantee Period, as these may run for 1, 2, 3, 4 or 5 Years in length.

Our table will look like this:

OrderID	CustomerID	ProductID	SaleQty	GuaranteeStartdate	GuaranteePeriod
1	1	HSC-010	1	01-01-2004	2
2	1	HYC-100	2	01-01-2004	1
3	2	LCD-001	1	25-10-2003	4
4	3	DMX-012	1	18-06-2003	3

Now to view the date when the Guarantee Renewal is due, we can create a query based upon this table, and use the DateAdd function to provide the data we need:

Query design, showing the additional dateadd function column added.

The additional column has been added to the query design, that will display the calculated renewal date - RenewalDate: DateAdd("yyyy",[GuaranteePeriod],[GuaranteeStartDate])

This uses the Interval in Years, the Number taken from the [GuaranteePeriod] field, and the Date from the [GuaranteeStartDate] field.

The resulting data is shown below:

The results of using the DateAdd function to display the Guarantee RenewalDate.