Problem with Null Values in a Date Field

  • 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
 
Here are three possible variations

Try using the NZ function
Maintenance =
IIF(Nz(MobileDate)>Nz(DemobDate),MobileDate,DemobDate)

OR
Nested IIf with IsNull
Maintenance =
IIF(IsNull(DemobDate),MobileDate,IIF(MobileDate>DemobDate,MobileDate,DemobDate))

OR
Maintenance =
IIF(IsNull(DemobDate) OR DemobDate<MobileDate,MobileDate,DemobDate)

Chris 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