Addition Query Question

[Rich S.]

Good Afternoon,

I have a query that pulls information from a time study. The innspector
looks at X number of parts over a time. Sometimes with breaks and lunches,
there are 4 sets of time (On1 & Off1), (On2 & Off2), (On3 & Off3), and (On4 &
Off4). These values are Date/Time in Medium Time (HH:MM AM/PM). Depending
on the size of the job and speed of the tech, not all the time sets are used.


When I tried to write an expression that would give me total value for times
I found something intersting. My equation is this:

Sub1:
(([Off1]-[On1])*24)+(([Off2]-[On2])*24)+(([Off3]-[On3])*24)+(([Off4]-[On4])*24)

Which returns nothing.

However, If I write a formula for a single set I get a result.

What is happening: if there was only a single time set, say only 9:00 Am to
10:00 am as On1 & Off1 with nothing in the other 3 sets, I get no answer.
Any Ideas?

Rich

 

[John Spencer]

Nulls propagate, so if any one of the fields in your equation is null
(blank) then you are going to get null (blank) as a result.

Best Idea would be to change your data structure so you only had On and Off
times in separate table.

Next idea is take a look at the NZ function so you can force zero values
into your equation
NZ(Off1-On1,0)*24 + NZ(Off2-On2,0) *24 + ...