Finding the Next Anniversary of a Given Date in Access 2010

In this article, you learn the steps of using Visual Basic for Applications (VBA) to find the next anniversary of a specified date.

The date functions built into VBA make it easy to perform calculations based on date arithmetic, such as finding the occurrence of a specific date within the current year. If you want to find the next anniversary of a date (to find a person's next birthday, for example), you must also consider whether the anniversary has already occurred in the current year.

Set up the Code

To use the sample code provided with this article in your own applications, you must start by either adding a module to your database, or finding an existing module into which to place the code. (Put the sample procedure in a standard module, not a class module, to get the most use from it.) To create a module, in Access 2010, click the Create tab, and then in the Macros & Code group, click Module. This action opens the VBA editor with the new module selected. To use an existing module, in the Macros & Code group, click Visual Basic to load the VBA editor, and then select the module. Either way, copy the code from the sample database into the selected module. (If you prefer, you can also merely import the module, AnniversaryCode, from the sample database into your own database.)

Using the Sample Code

The sample module includes the function NextAnniversary, which accepts a date as a parameter and returns the next anniversary of the supplied date. By using either the sample database or your own database that has the code inserted, you can interact with the function by using the Immediate window. Press Ctrl+G to display the Immediate window, and type the following lines. (Press Enter at the end of each line to display the function results in the Immediate window). Replace the parameter date in each example with a date before the current date, the current date, and then a date after the current date. Assuming that the current date is #3/24/2012#, try code such as the following (modifying the date formats to match your own locale).

? NextAnniversary(#3/21/1990#)
? NextAnniversary(#3/24/1990#)
? NextAnniversary(#3/30/1990#)

The first test returns the date 3/21/2013, because March 21 has already passed in the current year. The second example returns 3/24/2012, because the current date is March 24. Finally, the last example returns 3/30/2012, because March 30 has not yet occurred in the current year.

Examining the Sample Code

The NextAnniversary procedure resembles the following (with its introductory comments removed).

Public Function NextAnniversary(theDate As Date) As Date 
    Dim theDateThisYear As Date
    
    ' What is the corresponding date in the current year?
    theDateThisYear = DateSerial( _
     Year(Now), Month(theDate), Day(theDate))
    
    ' If the anniversary has already occurred, 
    ' add 1 to the year.
    If theDateThisYear < Date Then
        theDateThisYear = _
         DateAdd("yyyy", 1, theDateThisYear)
    End If
    NextAnniversary = theDateThisYear
End Function

The code starts by accepting a date value that contains the reference date for the function. The function uses the DateSerial function to construct the anniversary date in the current year, supplying values for the Year, Month, and Day functions.

theDateThisYear = DateSerial( _
 Year(Date), Month(theDate), Day(theDate))

The code uses the Year function to return the current year (using the Date function, which returns the current date), and the Month and Day functions to return the month and day parts of the supplied parameter date. The DateSerial function takes the three date portions, and creates a new Date variable that contains the anniversary date in the current year.

Next, the code compares the anniversary date in the current year with the current date.

If theDateThisYear < Date Then
  ' Code removed here...
End If

If the anniversary date in the current year is less than the current date, it means that the anniversary date has already occurred, and the code must add a year to the date. If that is the case, the sample executes the following statement, which adds the necessary year.

theDateThisYear = DateAdd("yyyy", 1, theDateThisYear)

The DateAdd function takes three arguments: A string template defining the date part to add to the supplied date ("yyyy" in this case, which indicates that you need to add years), a value indicating the number of the kind of date to add (1, in this case), and the starting date. The result is the anniversary date, but in the next year.

The function described here works not only in Microsoft Access 2010, but also in any modern VBA host product, including the products in Microsoft Office 2003 through Office 2010. We recommend taking the time to examine all the VBA date and time functions. They are surprisingly flexible and handle many situations you might not expect.

Length: 05:06

Grab the Code

• Access Developer Center

• Office Developer Center

• Microsoft Access Team Blog

About the Author

Ken Getz is a senior consultant with MCW Technologies and splits his time between programming, writing, and training. He specializes in tools and applications written in Visual Basic and Visual Studio. Ken is the coauthor of several best-selling books. They include VBA Developer's Handbook with Paul Litwin and Mike Gunderloy, Visual Basic Language Developer's Handbook with Mike Gilbert, and Access 2002 Developer's Handbook with Mike Gilbert (Sybex). He co-wrote several training courses for Application Developer's Training Company (www.appdev.com), including seminars for Visual Basic, ASP.NET, Access 2000 and 97, and Visual Basic 6 and 5. He has also recorded video training for AppDev, covering Visual Basic, ASP.NET, Visual Basic 6, and Access 2000 and 97. Ken is a frequent speaker at technical conferences and has spoken often at Microsoft's Tech·Ed conference. Ken is also a technical editor for Access-VB-SQL Advisor magazine and a columnist for Informant Publications' asp.netPRO magazine.