Minimum Date

[Brent]

I have a table with 3 date fields in it. I want to pull the earliest date
out of all three fields and put it in a field in a query.

example. Fields -CertExpDate1,CertExpDate2,CertExpDate3
Is there an easy function to do this?

Thanks
Brent

 

[Guest]

Try this

Select Date1,Date2,Date3, IIF(IIF(Date1 < Date2, Date1,Date2) < date3
,IIF(Date1 < Date2, Date1,Date2), Date3) as MinDate From TableName

 

[John Vinson]

I have a table with 3 date fields in it. I want to pull the earliest date
out of all three fields and put it in a field in a query.

example. Fields -CertExpDate1,CertExpDate2,CertExpDate3
Is there an easy function to do this?

Thanks
Brent

Not easy, because this table isn't correctly designed. If you have a
cert with three expdates, then you have a one to many relationship,
and should have a second table!

That said, some nasty nested IIF's can be used:

EarliestExpDate: IIF([CertExpDate1] < [CertExpDate2],
IIF([CertExpDate1] < [CertExpDate3], [CertExpDate1],
IIF([CertExpDate2] < [CertExpDate3], [CertExpDate2], [CertExpDate3],
IIF([CertExpDate2] < [CertExpDate3], [CertExpDate2],
IIF([CertExpDate1] < [CertExpDate3], [CertExpDate1], [CertExpDate3]))

John W. Vinson[MVP]

 

[John Spencer (MVP)]

I myself prefer to use the SWITCH function

EarliestDate:
Switch(CertExpDate1<=CertExpDate2 AND CertExpDate1<=CertExpDate3, CertExpDate1,
CertExpDate2<=CertExpDate3,CertExpDate2,
True,CertExpDate3)

Now that will fail if any of the three date fields are null, so you may have to
use NZ around each of your date fields in the comparison to take care of that
event.

I have a table with 3 date fields in it. I want to pull the earliest date
out of all three fields and put it in a field in a query.

example. Fields -CertExpDate1,CertExpDate2,CertExpDate3
Is there an easy function to do this?

Thanks
Brent

Not easy, because this table isn't correctly designed. If you have a
cert with three expdates, then you have a one to many relationship,
and should have a second table!

That said, some nasty nested IIF's can be used:

EarliestExpDate: IIF([CertExpDate1] < [CertExpDate2],
IIF([CertExpDate1] < [CertExpDate3], [CertExpDate1],
IIF([CertExpDate2] < [CertExpDate3], [CertExpDate2], [CertExpDate3],
IIF([CertExpDate2] < [CertExpDate3], [CertExpDate2],
IIF([CertExpDate1] < [CertExpDate3], [CertExpDate1], [CertExpDate3]))

John W. Vinson[MVP]

 

[Brent]

I just used those fields as examples. These dates actually correspond with
3 distinct types of insurance required for all of our subcontractors.

Thanks
Brent

I have a table with 3 date fields in it. I want to pull the earliest date
out of all three fields and put it in a field in a query.

example. Fields -CertExpDate1,CertExpDate2,CertExpDate3
Is there an easy function to do this?

Thanks
Brent

Not easy, because this table isn't correctly designed. If you have a
cert with three expdates, then you have a one to many relationship,
and should have a second table!

That said, some nasty nested IIF's can be used:

EarliestExpDate: IIF([CertExpDate1] < [CertExpDate2],
IIF([CertExpDate1] < [CertExpDate3], [CertExpDate1],
IIF([CertExpDate2] < [CertExpDate3], [CertExpDate2], [CertExpDate3],
IIF([CertExpDate2] < [CertExpDate3], [CertExpDate2],
IIF([CertExpDate1] < [CertExpDate3], [CertExpDate1], [CertExpDate3]))

John W. Vinson[MVP]

 

[Brent]

Works great thanks

Try this

Select Date1,Date2,Date3, IIF(IIF(Date1 < Date2, Date1,Date2) < date3
,IIF(Date1 < Date2, Date1,Date2), Date3) as MinDate From TableName

 

[John Vinson]

I just used those fields as examples. These dates actually correspond with
3 distinct types of insurance required for all of our subcontractors.

Fine. My argument still applies.

What happens when the rules change and the subs need a fourth kind of
insurance? Restructure all your tables, all your queries, dig through
all the queries and code to find all the references, ...?

You have a one (sub) to many (insurance) relationship. Model it that
way, and your queries will be MUCH simpler.

John W. Vinson[MVP]

 

[Guest]

The if statement worked for me also, except when the date field is blank.
The statement is obviously picking the null or blank as the earliest. How do
I get around this? I would like the output to ignore the blanks.
Thanks