Finding Min Date from Four Fields in a Record

[StephenB]

Good day.
I have a table with four date fields, of which at least one field will have a date.
I'm trying to write an IF Then statement on a query that will find the earliest date in all four fields.
I started off with ...
MinDate: IIf([Date1]<=[Date2],[Date1],(IIf([Date2]<=[Date3],[Date2],(IIf([Date3]<=[Date4],[Date3],[Date4])))))

...but that obviously didn't work

I've attached a sample db with the expected results.
Any help would be appreciated.
TIA

 

[Minty]

Daft question but - are what is the relation between these dates ? What is their purpose?
Frequently data in this format is not always stored correctly, which leads to the sort of issue you now have.

 

[StephenB]

The four dates indicate when four different categories were worked for each record (person.) So a record may indicate John Doe was reviewed for category 1 (Date1) on 1-15-2016 and then reviewed for category 3 (Date 3) 6-12-2016, the expected result would be 1-15-2016.

 

[Minty]

Okay so if you had a category test table that had the fields

CatTestID - PK
PersonID - FK - Links it back to your person
CatNo - Integer field - the test number 1, 2, 3, 4, 5, 6, etc
CatWorkedDate - Date

You could simply look up the first date for that person, and you would know the category number. If you ever add a 5 or 6th or 23rd category this layout accommodates it. You are also only storing the tests taken so can easily provide stats on them.

 

[JHB]

If you want to stick to your table design, the create an union query and use that for finding the minimum date.
Run the "TheFinalQuery" in the attached database.

 

[stopher]

You could add this function then use it in your query:

Public Function minDate(d1 As Date, d2 As Date, d3 As Date, d4 As Date) As Date

    minDate = d1
    If d2 < minDate Then minDate = d2
    If d3 < minDate Then minDate = d3
    If d4 < minDate Then minDate = d4

End Function

But I agree that with the others that your table should be better structured.

JHB's solution uses UNION to create the structure much like Minty suggests. Then uses DMIN hence showing how the right structure makes it easy to do what you want.

 

[pbaldy]

More flexible:

http://allenbrowne.com/func-09.html

But I agree with fixing the design.