Summing and Subtracting Fields

R

ridgerunner

I have an Access table containing the fields "Qty In" and "Qty Out". Only
"Qty In" has data in it, representing the beginning balance. I have written
a query to substract the sum of "Qty Out" from the sum of "Qty In" to return
a current balance for each product. My problem is, since the "Qty Out" field
contains no data the query does not return a balance. If I place a zero in
"Qty Out", the query works. Does this mean that every record will have to
contain data in both fields in order for the query to work correctly? It
seems a waste of time having to enter zero. Any help is greatly
appreciated.
 
M

Marshall Barton

ridgerunner said:
I have an Access table containing the fields "Qty In" and "Qty Out". Only
"Qty In" has data in it, representing the beginning balance. I have written
a query to substract the sum of "Qty Out" from the sum of "Qty In" to return
a current balance for each product. My problem is, since the "Qty Out" field
contains no data the query does not return a balance. If I place a zero in
"Qty Out", the query works. Does this mean that every record will have to
contain data in both fields in order for the query to work correctly? It
seems a waste of time having to enter zero. Any help is greatly
appreciated.
Click to expand...


You can use the Nz function in the expression to provide the
zero.
[Qty In] - Nz([Qty Out], 0)
 
R

ridgerunner

Thank you!

Marshall Barton said:
ridgerunner said:
I have an Access table containing the fields "Qty In" and "Qty Out". Only
"Qty In" has data in it, representing the beginning balance. I have
written
a query to substract the sum of "Qty Out" from the sum of "Qty In" to
return
a current balance for each product. My problem is, since the "Qty Out"
field
contains no data the query does not return a balance. If I place a zero in
"Qty Out", the query works. Does this mean that every record will have to
contain data in both fields in order for the query to work correctly? It
seems a waste of time having to enter zero. Any help is greatly
appreciated.
Click to expand...


You can use the Nz function in the expression to provide the
zero.
[Qty In] - Nz([Qty Out], 0)
Click to expand...
 
Top