ACC: How to Calculate a Credit Card Expiration Date (99941)

SUMMARY

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 shows you how to create a function 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])

Create a module with the following function:

   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

You can use the following derivative of the ExpirationDay() function in calculated fields in a query:

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

Use the following function, ExpirationDay(), in the AfterUpdate property of an expiration text box on your form or in a calculated field in a query. The function will return the date computed for the last day of the month/year.