Calculating a Future Date with the DateAdd Function

If you read my blog last week you may have seen the sample Lending Library Database that I created to demonstrate a working example of the DAO Recordset.  You may have noticed that I used a function called DateAdd to calculate the future date that a library book is due for return.  As you may remember, this date was automatically entered as the value for the DueDate field in the tblLoan junction table via VBA code.

I thought it would be interesting to take a closer look at how the DateAdd function works. However, please  do bear in mind that the DateAdd funciton can also be used in Queries and Calculated Form Controls (not just in VBA code).  This is how the syntax for DateAdd is constructed:

DateAdd("interval", number, date)

Figure 1 (above): This shows the DateAdd function used
as a Calculated Field in a Query.  You can see an example of a Calculated Field
in my post on the related DateDiff Function. (NB DateDiff calculates and returns the difference between 
two dates, whereas DateAdd calculates and returns a new date based on the value of the number parameter passed).

Figure 2: This shows the DateAdd function used as a Calculated Control in a form.  It can be entered
into the CONTROL SOURCE property or directly into the text box on the  Form Design Grid.

DateAdd Parameters

As you can see, the DateAdd function has three parameters - interval, number and date.  

Interval

The interval parameter determines whether we are adding intervals of days, weeks, or months etc.  The parameter is entered as a string value; days are entered as "d", weeks as "ww" and months as "m".  There are 10 possible parameter values to choose from, ranging from years "yyyy" right down to hours "h", minuets "n", and seconds "s".  

Number

The number parameter is the number of intervals to be added.  So if we want to calculate a date 30 days in the future, we would set the interval to "d" and the number to 30.

Date

The date parameter is the base date we are applying the DateAdd function to.  As such, if we want to calculate a date 30 days from 1/4/2012, we would simply enter 1/4/2012 as the date parameter value. If you want to calculate a date from today (whatever date that may be on the day you are reading this) you can enter the Date() function as the actual parameter value.  This will return the current date when the database is being run.

Calculating a Library Book Due Date

This is the syntax I used in the last weeks lending library database to calculate a DueDate 30 days from when a book is issued:

DateAdd ("d", 30, Date())

Figure 3: This shows the DateAdd function used in the fourth line of this VBA code snippet.
The rstLoan object is a DAO Recordset, and DueDate is the field name in the tblLoans Table.
You can see the rest of the code in last weeks blog post on the DAO Recordset.

As you can see, the interval used is days, the number parameter is 30, and I have used the Date() function to return the current date for the date parameter.