Chapter 28 Using the DateAdd Function
Chapter 28 Using the DateAdd Function
A
ccess provides a useful function that allows a time or date interval to be added or
subtracted to an existing date and time, giving the new date and time.Suppose your users need a report over a specified time interval. They want to be able to
pick a month and a year as the start point and then pull out all the data for the nextnmonths
(wherenis a variable supplied by the users) from the first day of that month.
Some complex processing is required to sort this out. Therefore, you need to know how
many days are in each month in order to know what to add to the starting point, which
basically means a lookup table of some sort.
However,DateAddprovides the functionality to easily work out the end date based on the
starting date and the number of months to increment.
Enter the following VBA code into a module:
Function TestDateAdd(Target, Months As Integer)
Dim Temp As String
If IsDate(Target) = False Then
TestDateAdd = "Invalid date"
Exit Function
End If
Temp = Right(Target, 2 )
If Temp <= 29 Then
Temp = " 20 " & Temp
301