Turn null values into zero somehow?

[torok]

Bug in Access? Turn null values into zero...

This should be easy.

I have two tables, "Orders" and "Trucks", related by an order_id field. I run a query that returns all Orders records and only Trucks records that match an order_id, then I group by order_id. So I end up with a table something like this (but with more fields):


order_id | Truck Hours | Truck Rate | Total: Truck Hours * Truck Rate
1 | 25 | 2 | 50
2 | | |
3 | 34 | 2 | 68

Note the blank number of truck hours for order 2. Is there some way to get a Zero in that spot, so I can use that field in my calculation? I tried, in the Total expression, nz((hours*rate),0) but got a crazy number....

Thanks for any help!

 

[torok]

Bug in Access?

Ok, this is very strange. I actually have three calculated "Totals" fields. The first two multiply different fields in the "trucks" table, and the third adds up those two totals. If I use Total1: nz(Sum(field1*field2),0) in the first one, Total2: nz(Sum(field3*field4),0) in the second, and Total3: Total1 + Total2 in the third, I get

$Total1,Total2.00 as my third total!

eg. Total1 = 50, Total2 = 200, Total3 = $50,200.00

Must be a bug in Access....??

 

[torok]

Solved ?

In case anyone's interested, you can't do this:

nz(Sum([x]*[y]),0) + nz(Sum([a]*),0) and hope to get the right answer. The two individual sums will be correct, but instead of adding them Access will concatenate the two sums.

You have to do Sum(nz([x],0)*nz([y],0)) + Sum(nz([a],0)*nz(,0))

That's just plain weird.

 

[RV]

Please post separately for different subjects.
Secondly, your Total3 field is NOT totalizing Total1 and Total2.
It's concatenating Total1 and Total2.

So, no bug in Access, just standard functionality....

RV

 

[torok]

My apologies - my posts were a bit slow in arriving to the forum, and I was confusing myself.

I'll try not to drag others down with me in the future.

 

[Jon K]

The nz() function should be used on the field itself, not on the calculated result. If you use it on the calculated result, what you get are characters, not numbers. That's why you've got a concatenation for Total3.

So, instead of writing nz(hour*rate,0), write it as:
hour*nz(rate)

Notice that the 0 argument in nz() can even be left out here since Access knows that rate is a numeric field and will return a numeric zero for any null values. Of course, you can always write nz(rate,0) if you like.