Min of three dates 1

[deharris2003]

Ok this one has been meking me crazy. I have three dates
dtmDate1
dtmDate2
dtmDate3

What I want to do is find the min date and the max date of the three dates that are available. All three dates are stored into seperate variables and my understanding is that the Min and Max functions only work if the three variables are stored in the same column. Please help

 

[TonyJollans]

Hi deharris2003,

You don't say what application. In Excel, the three (or more) dates can be in any individual cells, or variables. In Access you are talking about SQL functions so, yes, your dates would all have to be in the same column. It might be possible to do something for you if you post some more details, but if it's only three dates, explicit checks are probably the easiest way to go.

Enjoy,
Tony

 

[sfvb]

No, the MIN and MAX functions aren't confined to a single column, or a single row. The following examples all work:

=MIN(A1,B4,E7)
=MIN(A1:A45,B1:B45,E7)
=MAX(A1:A12)
=MAX(A1:E15,E22)

 

[deharris2003]

sorry, I am using MS Access ADOBD connection. Here is my SQL

strSQL = "SELECT Member_ID, Member_Name, Street_Address1, " & _
"Street_Address2, Zip, Home_Phone, Sex, Dob_Date, " & _
"Signup_Date, Resign_Date, Signup_Date1, Resign_Date1, " & _
"Signup_Date2, Resign_Date2, Pcp_ID, Pcp_Start_Date, " & _
"Pcp_ID3, Pcp_Start_Date2, Pcp_ID2, Pcp_Start_Date3, " & _
"PrimaryCare_OfficeVisit_Copay, " & _
"Specialist_OfficeVisit_Copay " & _
"FROM dbo.Cigna_Legacy "

Set rst = db.OpenRecordset(strSQL)

Then I set the variables to the three fields returned in the recordset

 

[deharris2003]

sfvb will this also work in Access? Can I do something like

Min(dtmDateq,dtmDate2,dtmDate3)

 

[TonyJollans]

Hi deharris2003,

You just have three dates - all in the same record. I don't think there's any shortcut but, to do it yourself isn't hard:

Function MinDate(d1 As Date, D2 As Date, D3 As Date) As Date
MinDate = IIf(d1 < D2, IIf(d1 < D3, d1, D3), IIf(D2 < D3, D2, D3))
End Function

You can do the max in the same way. Or, you can do a series of more explicit If .. Else structures if you want.

Enjoy,
Tony

 

[TonyJollans]

Hi deharris2003,

Or, if you want, you can set a Reference to Excel and do ..

Excel.WorksheetFunction.Min(dtmDate1, dtmDate2, dtmDate3)

.. but it's prety inefficient that way.

Enjoy,
Tony

 

[deharris2003]

Thanks Tony
This function works great!

 

[sfvb]

I'm an Oracle programmer, so I don't really know the answer to this, but:
Does Access really restrict you to using the same column?

Wouldn't:

SELECT MIN(dateA, MIN(dateB, dateC))
FROM mytable;

give you the minimum date across three seperate columns?

 

[TonyJollans]

Hi sfvb,

It's a while since I've worked with Oracle, but in Access MIN is an SQL aggregate function that takes one argument (column name) and returns the lowest value found in that column on records satisfying the selection criteria.

Enjoy,
Tony

 

[sfvb]

Hi Tony,

Your right. (I shouldn't post with only a couple hours sleep). I was thinking of the LEAST function not the MIN function.

 

[TonyJollans]

Hi sfvb,

That would do it (same way as Excel's MIN), but no Access equivalent I fear.

Enjoy,
Tony