Compare two dates and use the earlier date

[Bluesky]

Hi All,

I have two dates (date1 and date2).

I need to compare two separate dates and then create a new field (newdate)
which will contain the earlier date of the two.

Is this possible? Right now my dates are stored as a "short date," is that
format OK?

As always, thanks a bunch,

Blue Sky

 

[Bob Barrows]

Hi All,

I have two dates (date1 and date2).

I need to compare two separate dates and then create a new field
(newdate) which will contain the earlier date of the two.

Is this possible? Right now my dates are stored as a "short date,"
is that format OK?

select date1,date2,iif([date1]>[date2],[date2],[date1]) as newdate ...

 

[Bluesky]

Thanks Bob!

I'll try it.

I also just realized that I might be missing one of the two dates, so in
these instances I would like to return date1 or date2 as the newdate, do I
need to do something special for that to happen or will it do it
automatically?

Or there is the possibility that I might not have either date1 or date2,
again do I need to do anything special in these cases?

Thanks again!!!

BlueSky

Hi All,

I have two dates (date1 and date2).

I need to compare two separate dates and then create a new field
(newdate) which will contain the earlier date of the two.

Is this possible? Right now my dates are stored as a "short date,"
is that format OK?

select date1,date2,iif([date1]>[date2],[date2],[date1]) as newdate ...

 

[Bob Barrows]

Yes, there is definitely more work to do. First off, you need to decide
and tell us what needs to be in the newdate field if both date1 and
date2 are null.

Thanks Bob!

I'll try it.

I also just realized that I might be missing one of the two dates, so
in these instances I would like to return date1 or date2 as the
newdate, do I need to do something special for that to happen or will
it do it automatically?

Or there is the possibility that I might not have either date1 or
date2, again do I need to do anything special in these cases?

Thanks again!!!

BlueSky

Hi All,

I have two dates (date1 and date2).

I need to compare two separate dates and then create a new field
(newdate) which will contain the earlier date of the two.

Is this possible? Right now my dates are stored as a "short date,"
is that format OK?

select date1,date2,iif([date1]>[date2],[date2],[date1]) as newdate
...

 

[Bluesky]

Hi Bob,

Thanks again.

If there is only one date, I want the one date returned. would that happen
by automatically?

If there are no dates, then I just want a blank field returned, would that
also happen automatically?

Sorry, I'm still learning!

debbie

Thanks Bob!

I'll try it.

I also just realized that I might be missing one of the two dates, so in
these instances I would like to return date1 or date2 as the newdate, do I
need to do something special for that to happen or will it do it
automatically?

Or there is the possibility that I might not have either date1 or date2,
again do I need to do anything special in these cases?

Thanks again!!!

BlueSky

Hi All,

I have two dates (date1 and date2).

I need to compare two separate dates and then create a new field
(newdate) which will contain the earlier date of the two.

Is this possible? Right now my dates are stored as a "short date,"
is that format OK?

select date1,date2,iif([date1]>[date2],[date2],[date1]) as newdate ...

 

[Bob Barrows]

Sorry for the delay. This will be a complex series of nested iif statements.
I will see if I can use indentation to make clear what is going on:

select date1,date2
,iif(
[date1] is null and [date2] is null
,null
, iif([date1] is not null and [date2] is not null
,iif([date1]>[date2], [date2], [date1])
, iif([date1] is null,[date2],[date1])
)
) as newdate
....

Hi Bob,

Thanks again.

If there is only one date, I want the one date returned. would that
happen by automatically?

If there are no dates, then I just want a blank field returned,
would that also happen automatically?

Sorry, I'm still learning!

debbie

Thanks Bob!

I'll try it.

I also just realized that I might be missing one of the two dates,
so in these instances I would like to return date1 or date2 as the
newdate, do I need to do something special for that to happen or
will it do it automatically?

Or there is the possibility that I might not have either date1 or
date2, again do I need to do anything special in these cases?

Thanks again!!!

BlueSky

Bluesky wrote:
Hi All,

I have two dates (date1 and date2).

I need to compare two separate dates and then create a new field
(newdate) which will contain the earlier date of the two.

Is this possible? Right now my dates are stored as a "short date,"
is that format OK?

select date1,date2,iif([date1]>[date2],[date2],[date1]) as newdate
...

 

[John Spencer]

I would use this expression. As long as either date cannot be after Dec
31, 3000 then this should work for you.

IIF(Nz(Date1,#12/31/3000#)<Nz(Date2,#12/31/3000#),Date1,Date2)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Bob Barrows]

Of course! Jeez I can be dense sometimes ...

I would use this expression. As long as either date cannot be after
Dec 31, 3000 then this should work for you.

IIF(Nz(Date1,#12/31/3000#)<Nz(Date2,#12/31/3000#),Date1,Date2)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Bluesky]

Thanks Bob and John,

You guys are great!

Thanks for taking the time to help me out!

I'll let you know how it goes.

debbie