Compare two dates and use the earlier date

  • Thread starter Thread starter Bluesky
  • Start date Start date
B

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
 
Bluesky said:
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?
Click to expand...
select date1,date2,iif([date1]>[date2],[date2],[date1]) as newdate ...
 
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

Bob Barrows said:
Bluesky said:
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?
Click to expand...
select date1,date2,iif([date1]>[date2],[date2],[date1]) as newdate ...
Click to expand...
 
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

Bob Barrows said:
Bluesky said:
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?
Click to expand...
select date1,date2,iif([date1]>[date2],[date2],[date1]) as newdate
...
Click to expand...
Click to expand...
 
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




Bluesky said:
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

Bob Barrows said:
Bluesky said:
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?
Click to expand...
select date1,date2,iif([date1]>[date2],[date2],[date1]) as newdate ...
Click to expand...
Click to expand...
 
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




Bluesky said:
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

Bob Barrows said:
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
...
Click to expand...
Click to expand...
Click to expand...
 
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
'====================================================
 
Of course! Jeez I can be dense sometimes ...

John said:
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
'====================================================
Click to expand...
 
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
 
Back
Top