Using Nz with Format + CDate + HH:nn:ss problem

[neelsfer]

The following code does not ignore the empty field values.It provides the correct values when the fields do contain a time value (ie 10:20:35)

TotalTime3: Format$(Nz(CDate([lap1time]))+Nz(CDate([lap2time]))+Nz(CDate([lap3time]))+Nz(CDate([lap4time])),"hh:nn:ss")

(for illustration purposes i used the first 4 fields only - are actually 8)
Any suggestion on how to ignore empty fields in this query please?

[MMcCarthy]

You are not using the NZ function correctly. The function is nz(CDate([lap1time]),"Value if Null").

However, in this case I don't think you need it. If the field values are null then just using the + sign should deal with it.

TotalTime3: Format$(CDate([lap1time])+CDate([lap2time])+CDate([lap3time])+CDate([lap4time]),"hh:nn:ss")

[neelsfer]

The timefield values are blank as it is generated in a query. I need to use

TotalTime3: Format$(Nz(CDate([lap1time]))+Nz(CDate([lap2time]))+Nz(CDate([lap3time]))+Nz(CDate([lap4time])),"hh:nn:ss")

and incorporate Nz or equivalent to compensate for the blank values. Any suggestions please?

[MMcCarthy]

Did you try using 0 for the value is null property?

TotalTime3: Format$(Nz(CDate([lap1time]),0)+Nz(CDate([lap2time]),0)+Nz(CDate([lap3time]),0)+Nz(CDate([lap4time]),0),"hh:nn:ss")

I'm not sure I understand why the + sign on it's own doesn't work though, it should just ignore the null values.

[neelsfer]

this is so weird!
if i have lap1time to lap4time and no blank spaces, it works 100%
If i have only 3x laptimes and 1 blank space, it gives an error
see pics

[ADezii]

You are a victim of NULL Propagation, without using Nz(), if any part of an Expression evaluates to NULL, the entire expression will return NULL as in:

Format$(CDate("00:51:54") + NULL + CDate("00:00:03"), "hh:nn:ss")

Returns:

NULL

[neelsfer]

Any solution you may have in mind?
This sounds like a serious cancer to me!

[ADezii]

Here is the Loooooooooooong answer for 5 Laps, I had to leave some fun for you! (LOL).

TotalTime: Format$(IIf(IsNull([LapTime1]),0,CDate([LapTime1]))+IIf(IsNull([LapTime2]),0,CDate([LapTime2]))+IIf(IsNull([LapTime3]),0,CDate([LapTime3]))+IIf(IsNull([LapTime4]),0,CDate([LapTime4]))+IIf(IsNull([LapTime5]),0,CDate([LapTime5])),"hh:nn:ss")