Null Values in Date Calculation

  • Thread starter Thread starter Chris Roche
  • Start date Start date
C

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] -
Click to expand...
[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
..
 
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

Chris Roche said:
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] -
Click to expand...
[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
.
Click to expand...
 
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

Chris Roche said:
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] -
Click to expand...
[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
Click to expand...
 
Back
Top