How do I add together more than 2 fields? I have number fields.

S

stevemetsch

I have several fields listing different sizes of T shirts that are all the
same price. I want to add the fields (of each record) together and then
multiply by the price. I tried normal mathematical notation without any
success.
 
M

Michel Walsh

You probably have null (no value) for some quantity. Try:


( (Nz( qty1, 0) + Nz( qty2, 0) + ... + Nz( qtyN, 0) ) * unitPrice


Vanderghast, Access MVP
 
J

John W. Vinson

I have several fields listing different sizes of T shirts that are all the
same price.
Click to expand...

In that case your table structure is WRONG. "Fields are expensive, records are
cheap"; you should have one *record* per item (size of tshirt), not a separate
field for each size.
I want to add the fields (of each record) together and then
multiply by the price. I tried normal mathematical notation without any
success.
Click to expand...

You'll get a NULL result if any of the individual field values is NULL (say
you have a field for XXXL but nothing in that field). You can use the NZ
function to convert Null to Zero, e.g.

(NZ([XS]) + NZ() + NZ([M]) + NZ([L]) + NZ([XL])) * [UnitCost]

but you'll be much better off having five records instead of five fields, and
using a Totals query to sum them.
 
Top