ACC: How to Calculate a Credit Card Expiration Date

• Microsoft Access 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

If you enter a credit card expiration date (month/year) in a date/time field on a form, Microsoft Access assumes that the card expires on the first day of the month. This article describes a function that you can use to correctly calculate the expiration date as the last day of the month.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

Use the following function, ExpirationDay(), in the AfterUpdate property of an expiration text box on your form. The function will replace the date entered in the text box with the date computed for the last day of the month/year.

Given a date/time field bound to a text box with the ControlName property set to "Expiration," the AfterUpdate property should read:

   =ExpirationDay([expiration])

   Option Explicit
   Function ExpirationDay (MyDate as Control)
      Dim NextMonth
      If IsNull(MyDate) Then Exit Function
      NextMonth = DateAdd("m", 1, MyDate)
      MyDate = NextMonth - DatePart("d", NextMonth)
   End Function

   Function ExpirationDay (MyDate)
       Dim NextMonth
       If IsNull(MyDate) Then Exit Function
       NextMonth = DateAdd("m", 1, MyDate)
       ExpirationDay = NextMonth - DatePart("d", NextMonth)
   End Function