Others

Validate Month-End Expiration Dates in Microsoft Access

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 869 views

Question details

Require an expiration date entered in an Access form to be the last day of the current month or a future month.

Validate Month-End Expiration Dates in Microsoft Access
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the form's event settings

Open the form in Design View, select the form, and open the Property Sheet. On the Event tab, find Before Update.

2
Create the event procedure

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.

3
Add the validation code

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

4
Test the form

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.

Validate the expiration date in the form's Before Update event
Scope of validation: This event applies when records are saved through this form. It does not validate changes made directly to the table or through other forms.
Free Microsoft Office alternative

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.

Organize expiration dates in a spreadsheet.Open and edit Microsoft Excel XLSX files for easier migration.Work with a familiar, easy-to-use office interface.Use a free, lightweight office suite.
microsoft office alternative - wps office

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.