Microsoft Access VBA Macro Programming

Else

Temp = " 19 " & Temp

End If

Target = Month(Target) & "/" & Temp

TestDateAdd = DateAdd("m", Months + 1, Target)

TestDateAdd = DateAdd("d", -1, TestDateAdd)

End Function

This function accepts the parameters of a date and the number of months to increase it by.

Notice that the Target parameter is not defined as a date but as a variant. This is because your

users may enter a date in a format that VBA cannot understand. If we define Target as a date,

there will be a type mismatch error before the function even begins to run, and thus no

opportunity for error trapping.

First, there is a procedure using theIsDatefunction to test whether a valid date has been

passed across. For example, if Target contains “Next week”, it will fail at this point and exit

the function, returning the string “Invalid Date”.

However, interestingly enough, if the user puts in 31-Nov-09, theIsDatefunction will

consider this a valid date! The only problem is that it will interpret the year incorrectly. If the

user enters 31-Nov-2009 (with a four-digit year), thenIsDatewill correctly interpret that the

date is invalid.

Because Access VBA has problems interpreting dates with two-digit years, a procedure

can be used to change the date to a four-digit year. The procedure tests the two right-hand

characters of target to see if they are less than or equal to 29. If this is true, then the year is

made a four-digit year commencing with “20”, otherwise it becomes a four-digit year

beginning with “19”.

The Target string is then set to the month and the four-digit year. For the purposes of the

DateAddfunction, the day will default to the first day, so long as a four-digit year is used.

TheDateAddfunction is used to add the number of months parameter to the start date.

The “m” means add in months, and the number of months plus 1 is added to the target date.

The reason we add an extra month is because we are trying to find the last day of the final

month. You can do this by adding an extra month, which takes the calculation to the first day

of the next month. The same methodology is used to subtract one day, taking it back to the

final day of the last month.

Regardless of your locale, as long as you enter a date in your local format, this will return

the last day of the monthnmonths ahead.

Even if you enter “31-Nov-09”, it will still be interpreted as “01-Nov-2009” and will

return “28-Feb-2010” if the months parameter is set to 3.

302 Microsoft Access 2010 VBA Macro Programming