You are here:

Using MS Access/Finding the earliest Date field in an access record


My problem is determining the earliest date from a number of fields (5) within a record to display in another column in the Table.

I currently have a table called “tblElectricalMaintenance” that is designed to record dates that different types of electrical maintenance have been carried out.

The fields are as follows: Alternator, HourMeter, 900Enclosure, PJBSwitch and ReverseAlarm. These maintenance activities are carried out on different dates. However I have a need to know when the first Electrical Maintenance activity (whichever of the 5), occurred and then report on it

Can I use a query to determine the required outcome or should I use VBA? I do no know what criteria to put in the query and also if I use VBA what would the code look like.

I have a little experience with VBA however; I have no clue as how to start this code. I thought of using a nested “IIf Function”, but that got too complicated.

Can you help me?


Well your problem is basically that you have a denormalized structure. If your database was designed properly this would be easy.

You have made a common mistake for novices in that you created a horizontal structure using field names to define data rather than a vertical structure. You should have two tables here. A lookup table for maintenance types:

MaintenanceTypeID (PK Autonumber)

Then your tblElectricalMaintenance should look like this:

ElectricalMaintenanceID (PK Autonumber)
ParentID (Foreign Key to parent record
MaintenanceTypeID (FK)

From there it would be very easy to create a query to return the earliest date. Or you could simply use a DMin() function

And, by the way, you shouldn't store this value.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.