Microsoft KB Archive/99941

= ACC: How to Calculate a Credit Card Expiration Date =

Article ID: 99941

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q99941



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



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 &quot;Building Applications with Microsoft Access&quot; 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 &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; 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 &quot;Expiration,&quot; 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(&quot;m&quot;, 1, MyDate) MyDate = NextMonth - DatePart(&quot;d&quot;, 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(&quot;m&quot;, 1, MyDate) ExpirationDay = NextMonth - DatePart(&quot;d&quot;, 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.

Additional query words: date

Keywords: kbhowto kbprogramming KB99941

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.