Validate Month-End Expiration Dates in Microsoft Access
Question details
Require an expiration date entered in an Access form to be the last day of the current month or a future month.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A user enters an expiration date in a form and saves the record.
- Observed behavior
- The form should accept month-end dates for the current or a future month and reject other dates.
Confirm that the expiration date control is bound to a Date/Time field, and check its name in the form's Property Sheet. The example uses txtMyDate; replace it with your control name if needed.
Validate the expiration date in the form's Before Update event
Use VBA to prevent a record from being saved unless its expiration date is an eligible month-end date.
The code calculates the first day of the following month and subtracts one day. It compares the date portion of the entered value with that result and rejects dates before today.
Open the form in Design View, select the form, and open the Property Sheet. On the Event tab, find Before Update.
Set Before Update to [Event Procedure] and click the ellipsis button to open the VBA editor. If a Form_BeforeUpdate procedure already exists, incorporate the checks into it rather than creating a duplicate.
In the VBA editor, add this procedure, replacing txtMyDate with your control name: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim enteredDate As Date Dim lastDay As Date If IsNull(Me.txtMyDate) Then MsgBox "Enter an expiration date." Cancel = True Exit Sub End If enteredDate = DateValue(Me.txtMyDate.Value) lastDay = DateSerial(Year(enteredDate), Month(enteredDate) + 1, 1) - 1 If enteredDate <> lastDay Or enteredDate < Date Then MsgBox "Enter the last day of the current month or a future month." Cancel = True End If End Sub
Save the form, switch to Form View, and try saving records with a current month-end date, a future month-end date, a midmonth date, and a past month-end date.

Track Expiration Dates in WPS Spreadsheets
WPS Office is a free, lightweight alternative for working with office files in a familiar interface. Use WPS Spreadsheets to track expiration dates and migrate compatible Excel files; Access is still required for the database form and its VBA validation.

Frequently Asked Questions
How does the VBA code find the last day of a month?
DateSerial(Year(enteredDate), Month(enteredDate) + 1, 1) returns the first day of the following month. Subtracting 1 returns the selected month's final day, including for February and December.
Will a past month-end date be accepted?
No. The condition enteredDate < Date rejects dates before today, so a month-end date from a past month cannot be saved through the form.
What happens if the expiration date is blank?
When the form's Before Update event runs, the IsNull check displays a message and cancels the save. For table-level protection, also set the date field's Required property to Yes in table Design View.
Does this validation apply to changes made outside the form?
No. Form_BeforeUpdate runs for saves through that form. Direct table edits and other forms need their own validation or a table-level constraint.




