Zero Value Fields

[Guest]

We have built a query to comine data from two tables. One has a complete parts listing with the budget and the other has the actual shipments. The query combines the two, inserting a 0 when there is no shipment to match a budgeted amount. Then we have added a calculation field to take the budget qty minus the actual qty. On the lines where the shipment has an inserted 0, The calculation results show a 0 instead of the negative budget amount as we had expected. The formating i
#,##0;(0);#;0 for the actual shipments. Does anyone have any ideas how to make this work? I understand that if I generated a report that the calculation could be done there but I was hoping to make it work within the query

Thanks for any help!

 

[Guest]

Try NZ(budget qty) - NZ(actual qty)

The NZ function will convert any non numeric value in the
calculation to zero.

-----Original Message-----
We have built a query to comine data from two tables. One

has a complete parts listing with the budget and the other
has the actual shipments. The query combines the two,
inserting a 0 when there is no shipment to match a
budgeted amount. Then we have added a calculation field to
take the budget qty minus the actual qty. On the lines
where the shipment has an inserted 0, The calculation
results show a 0 instead of the negative budget amount as
we had expected. The formating is

#,##0;(0);#;0 for the actual shipments. Does anyone have

any ideas how to make this work? I understand that if I
generated a report that the calculation could be done
there but I was hoping to make it work within the query.

 

[John Vinson]

We have built a query to comine data from two tables. One has a complete parts listing with the budget and the other has the actual shipments. The query combines the two, inserting a 0 when there is no shipment to match a budgeted amount. Then we have added a calculation field to take the budget qty minus the actual qty. On the lines where the shipment has an inserted 0, The calculation results show a 0 instead of the negative budget amount as we had expected. The formating is
#,##0;(0);#;0 for the actual shipments. Does anyone have any ideas how to make this work? I understand that if I generated a report that the calculation could be done there but I was hoping to make it work within the query.

Thanks for any help!

The Format does not affect what's *stored* or calculated - only what's
shown on the Report. Null is still Null, and any expression involving
a Null returns Null!

Use the NZ() function to convert Null to Zero in your expression:

[BudgetAmt] - NZ([ActualAmt])