Summing and Subtracting Fields

  • Thread starter Thread starter ridgerunner
  • Start date Start date
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.
 
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)
 
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...
 
Back
Top