Compare Dates multiple fields select earliest

[John1Chr]

In a table that is named Budget I have Six different dates in Six Different fields. Well, the fields can vary whether or not they are null for each record. Each one of the fields could be null. Actually, I glanced quickly at the 32,000 records and noticed that one of the records had all 6 fields as being null. What is the best way to capture the earliest date of the six fields when comparing all of the dates within one record. Maybe we could create a seventh field to contain the earliest date?

Example: a record in the Budget table contains:

Field 1 01/01/2004
Field 2 04/31/2002
Field 3 02/04/2000
Field 4 null
Field 5 null
Field 6 01/05/1999

I want to capture the earliest date which is in Field 6 (01/05/1999) after comparing all of the dates in the record. What is the best way to do this?

 

[PHV]

Create your own function:
'A generic function to get the min value of an arbirtrary numbers of same type values:
Public Function MyMin(ParamArray Args())
Dim i As Long, rv
If UBound(Args) >= 0 Then rv = Args(LBound(Args))
For i = 1 + LBound(Args) To UBound(Args)
If Trim(rv & "") = "" Or (rv > Args(i) And Trim(Args(i) & "") <> "") Then rv = Args(i)
Next
MyMin = rv
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[John1Chr]

I am still learning. How do I put the fields into this function? I am trying to put it into a select query and maybe this is not the correct way to do it? I saved the function in a module. Do I run the function on each field? (ie. MyMin([Field1])..MyMin([Field2]). The fields represent different dates. One field is the authorization date another is a shipping date etc….

 

[PHV]

SELECT MyMin(Field1,Field2,Field3,Field4,Field5,Field6) AS EarliestDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[John1Chr]

Thanks PHV! It worked perfectly.