DateAdd( )

For adding and subtracting dates and times, the easiest function to use is the DateAdd() function. This is very similar to the functions AddDays(), AddMonths(), etc. found in the .NET DateTime class. Using the DateAdd() function requires passing a string representing the type of interval to modify, the number of units to add or subtract, and the date to modify. There are a number of different strings that designate the interval to modify. The interval strings are listed in Table 9-11. To subtract a date interval pass a negative number of units. The DateAdd() function returns a DateTime value and this may need to be converted to either a Date or a Time depending on how you intend to use the result.

Using one q interval unit is the same as using three m intervals. The benefit of using the q interval is that many financial reports are printed on a quarterly basis. After the user is prompted for how many quarters they wish to print, you can take their input and use it to calculate a final date. Although multiplying their input by 3 is fairly trivial, having a shortcut is nice and it helps makes your code self-documenting.

Rather than using the DateAdd() function to add and subtract days, it is just as acceptable to directly add a number to the Date variable. Since the date is stored as a number, adding another number to it will increase the date by that number of days. The following examples both produce the same result.

Formula = DateAdd("d", 10, #1/1/2002#) ‘Returns 1/11/2002
Formula = #1/1/2002# + 10 ‘Returns 1/11/2002

The benefit of using the DateAdd() function is that it takes into account how many days are in each month and it checks for valid dates. As an example, say that you want to find out the last day of the next month. To do this with the addition operator, you need to know how many days are in the next month so you will probably store that information in an array. You also need to track which years are leap years. Using the DateAdd() function is much easier because if you add one month to the current date, it will check that this returns a valid date. If there aren’t enough days in the month, it will return the last valid day of the month. The same applies to using the quarter interval. The function adds three months to the current date and makes sure that this is a valid date. If not, it returns the last valid date of the quarter.