How to ignore an #Error in a query field you are sorting, descending?

[neelsfer]

When i try and add up 3 fields in a query and 1 field is blank, i a get #Error.(due to complicated string problems)

This is fine, but now i want to sort the rest of the records that do add up, and then ignore the one in this descending query, that has an empty field preventing it from been added up.See screen capture pics.
I will be happy just to sort those with totals, as an athlete have to finish all 3 days to be in contention for the overall prize.I you finish only 1 or 2 days, you are disqualified anyway and the total time will be ignored.

fields to add up are

=day1time + day2time + day3time

the following formulas add up but dont ignore the empty spaces. needs to be modified

TT: Format$(IIf(IsNull([Day1Time]),0,CDate([Day1Time]))+IIf(IsNull([Day2Time]),0,CDate([Day2Time]))+IIf(IsNull([Day3Time]),0,CDate([Day3Time])),"hh:nn:ss")

have also tried:

TotalTime: Format$(Nz(CDate([Day1time]))+Nz(CDate([Day2time]))+Nz(CDate([Day3time])),"hh:nn:ss")

i get the Day1-3 times using the following formula

Day1Time: Nz(Format([Day2]-[Day1],"hh:nn:ss")) etc etc

Should i take this query to a form datasheet?
any suggestions

[orangeCat]

You haven't shown how your fields are defined.
Here is a sample using the following data.

Table: PersonTime
Fields:
ID autonumber KEY
FName text
LastName text
Day1Time number long integer
Day2Time number long integer
Day3Time number long integer

My intent would be to store the Times in Seconds
and calculate the results.

The data, including a time of 0, are
1
Joe
Smith
10
78
0 <-----) time recorded

2
Tom
Jones
74
127
60

The query:

SELECT PersonTimes.id
, PersonTimes.Fname
, PersonTimes.LastName
, PersonTimes.Day1Time
, PersonTimes.Day2Time
, PersonTimes.Day3Time
, Format(CDate([Day1Time]+[Day2Time]+[day3Time])/(60*60*24),"hh:nn:ss") AS TotTime
FROM PersonTimes;

The rationale is to sum the number of seconds in the calculation; convert it to a date by
dividing the total seconds by
(seconds per minute *minutes per hour * hours per day)
to get days and to format as hh:nn:ss

[ADezii]

It appears to work well in the following context (Attachment):

[neelsfer]

Let me attach the problem

[ADezii]

There is no Code attached to the Click() Event of the Command Button, what Query is causing the problem?

[neelsfer]

sorry its the conversion to 2003 that did that
Rt_StageTotal1Q

[ADezii]

I do believe that the problem lies in the [T1T] Calculated Field (RT_StageTotal1Q)in that it explicitly refers to the [Day2Time] and [Day3Time] Calculated Fields directly. I've substituted the Expressions contained within those Referenced Calculated Fields, and I do believe that it solved the problem. I'll post the revised SQL below as well as the Revised Attachment.

SELECT Format$(IIf(IsNull([Day1Time]),0,CDate([Day1Time]))
+IIf(IsNull([Day2]-[Day1]),0,CDate([Day2]-[Day1]))
+IIf(IsNull([Day3]-[Day2]),0,CDate([Day3]-[Day2])),"hh:nn:ss") AS T1T, 
Nz(Format([Day2]-[Day1],"hh:nn:ss")) AS Day2Time, Cyclist.Name, 
Nz(Format([Day3]-[Day2],"hh:nn:ss")) AS Day3Time, Cyclist.Surname, Cyclist.Gender, RaceEntry.RaceNo, RaceEntry.IDNo, Rt_Stage1TimesQ.Day1Time, Rt_Stage1TimesQ.ShortMultidayName, Rt_Stage1TimesQ.MultidayStart, Rt_Stage1TimesQ.Day1, Rt_Stage2TimesQ.Day2, Rt_Stage3TimesQ.Day3
FROM (((RaceEntry LEFT JOIN Rt_Stage1TimesQ ON RaceEntry.IDNo = Rt_Stage1TimesQ.IDNo) LEFT JOIN Rt_Stage2TimesQ ON RaceEntry.IDNo = Rt_Stage2TimesQ.IDNo) LEFT JOIN Rt_Stage3TimesQ ON RaceEntry.IDNo = Rt_Stage3TimesQ.IDNo) INNER JOIN Cyclist ON RaceEntry.IDNo = Cyclist.IDNo
GROUP BY Nz(Format([Day2]-[Day1],"hh:nn:ss")), Cyclist.Name, Nz(Format([Day3]-[Day2],"hh:nn:ss")), Cyclist.Surname, Cyclist.Gender, RaceEntry.RaceNo, RaceEntry.IDNo, Rt_Stage1TimesQ.Day1Time, Rt_Stage1TimesQ.ShortMultidayName, Rt_Stage1TimesQ.MultidayStart, Rt_Stage1TimesQ.Day1, Rt_Stage2TimesQ.Day2, Rt_Stage3TimesQ.Day3;

[neelsfer]

Thx mr Adezii; will have a look.

[neelsfer]

working 100%; you are star - mr Adezii!!

[ADezii]

Glad we got it working for you.