Null Values in Date Calculation

[Chris Roche]

Hello all,

I am having a problem getting the proper date. What I want
to happen is a date field called [Maintenance] would take
the more recent date of two other fields [MobileDate] and
[DemobDate]. Is there is quick way of doing this, even if
one or the other may be null? I think I may have done it
the roundabout way. I created a field called Recent which
is as follows: Recent: DateDiff("d",[MobileDate],
[DemobDate]), which essentially calculates the difference
between the 2 dates. Then I used Maintenance: IIf([Recent]

0,[MobileDate],[DemobDate])so that if the [DemobDate] -

[MobileDate] is positive it takes the [DemobDate], and if
not then it takes the [MobileDate]. That all works fine
except when the [DemobDate] is Null, then nothing is input
into [Maintenance]. When [MobileDate] is null it gives the
proper [DemobDate] in [Maintenance]. Like I said I will
take an easier route if there is one. Its just when there
is a null value in DemobDate that a problem occurs.

Thanks for the help,
Chris
..

 

[Kelvin]

Chris, didn't you post this question before?

Access stores dates as numbers so the easy way would be to compare the
number version of the date. Date/Time data are stored with the integer part
of the number as the date and the decimal part as the time. Since you are
interested in comapring dates, first we need to drop the decimal part with
the CINT function. Then doing a simple comaprison

Hello all,

I am having a problem getting the proper date. What I want
to happen is a date field called [Maintenance] would take
the more recent date of two other fields [MobileDate] and
[DemobDate]. Is there is quick way of doing this, even if
one or the other may be null? I think I may have done it
the roundabout way. I created a field called Recent which
is as follows: Recent: DateDiff("d",[MobileDate],
[DemobDate]), which essentially calculates the difference
between the 2 dates. Then I used Maintenance: IIf([Recent]

0,[MobileDate],[DemobDate])so that if the [DemobDate] -

[MobileDate] is positive it takes the [DemobDate], and if
not then it takes the [MobileDate]. That all works fine
except when the [DemobDate] is Null, then nothing is input
into [Maintenance]. When [MobileDate] is null it gives the
proper [DemobDate] in [Maintenance]. Like I said I will
take an easier route if there is one. Its just when there
is a null value in DemobDate that a problem occurs.

Thanks for the help,
Chris
.

 

[Kelvin]

Ignore previous post. Hit send by accident.

Chris, didn't you post this question before?

Access stores dates as numbers so the easy way would be to compare the
number version of the date. Date/Time data are stored with the integer part
of the number as the date and the decimal part as the time. Since you are
interested in comapring dates, first we need to drop the decimal part with
the CINT function. Then doing a simple comaprison will tell which is
greater. Put all this in an IIF statement and you get your result.

MaxDate:IIF(CInt([MobileDate])>CInt([DemobDate]),[MobileDate],[DemobDate])

Kelvin

Hello all,

I am having a problem getting the proper date. What I want
to happen is a date field called [Maintenance] would take
the more recent date of two other fields [MobileDate] and
[DemobDate]. Is there is quick way of doing this, even if
one or the other may be null? I think I may have done it
the roundabout way. I created a field called Recent which
is as follows: Recent: DateDiff("d",[MobileDate],
[DemobDate]), which essentially calculates the difference
between the 2 dates. Then I used Maintenance: IIf([Recent]

0,[MobileDate],[DemobDate])so that if the [DemobDate] -

[MobileDate] is positive it takes the [DemobDate], and if
not then it takes the [MobileDate]. That all works fine
except when the [DemobDate] is Null, then nothing is input
into [Maintenance]. When [MobileDate] is null it gives the
proper [DemobDate] in [Maintenance]. Like I said I will
take an easier route if there is one. Its just when there
is a null value in DemobDate that a problem occurs.

Thanks for the help,
Chris