Calculations in a query not working if nothing is entered into field - How to solve?

  • Thread starter Thread starter gadavis1701
  • Start date Start date
G

gadavis1701

I am new to Access and have designed a database by the seat of my
pants.

I have a problem with a database I designed. For each record (called
Product) I am trying to add several fields together (call them Qty1,
Qty2, and Qty3) to get a total (called TotalQty). There may or may not
be values for the Qty fields. If there are no entries for Qty1 and Qty3
but there is for Qty2, I still want the three fields added together for
a total.

For example, here is a table of what I want to accomplish.
Product | Qty1 | Qty2 | Qty3 | TotalQty
A01 | | 40 | | 40
A02............20.......10.........30.......60

However, if there is nothing entered for Qty1 and Qty3 fields I don't
want a zero to be entered by default. It messes up a software
applications that pulls data from this database.

If I remove the zeroes from the Default Value parameter of the table
and from the table entry for Qty1 and Qty3, the query can't add the
fields together and display a result. If there are zeroes, the query
works perfectly.

How can I solve this?

I hope this makes sense.

Any help would be greatly appreciated.
 
Sorry, the table came out a little messed up.

Here it is again hopefully a little cleaned up and more readable:

Product.|.Qty1.|.Qty2.|.Qty3.|.Total
A01.....|......|.40...|......|.40
A02.....|.20...|..10..|.30...|..60

Hope this helps clarify things.
 
I am new to Access and have designed a database by the seat of my
pants.

I have a problem with a database I designed. For each record (called
Product) I am trying to add several fields together (call them Qty1,
Qty2, and Qty3) to get a total (called TotalQty). There may or may not
be values for the Qty fields. If there are no entries for Qty1 and Qty3
but there is for Qty2, I still want the three fields added together for
a total.

For example, here is a table of what I want to accomplish.
Product | Qty1 | Qty2 | Qty3 | TotalQty
A01 | | 40 | | 40
A02............20.......10.........30.......60

However, if there is nothing entered for Qty1 and Qty3 fields I don't
want a zero to be entered by default. It messes up a software
applications that pulls data from this database.

If I remove the zeroes from the Default Value parameter of the table
and from the table entry for Qty1 and Qty3, the query can't add the
fields together and display a result. If there are zeroes, the query
works perfectly.

How can I solve this?

I hope this makes sense.

Any help would be greatly appreciated.
Click to expand...

Look up the NZ function in VBA help.

Exp:Nz([FieldA]) + Nz([FieldB]) + etc.
 
Karl,
Thanks for the quick reply.
Would I enter this in the TotalQty field of the table or the Query?

Plus what exactly does this expression do? Is the Sum part necessary?
 
Ok you do not need the Sum. Put it in a column of you query.

Open the query in design view and add the column.
 
If you use the Nz functino in a query, it must have the value if null
argument or it will return a zero length string and not the results you
expect. So, to do this in a query:

Total: Sum(Nz([Qty1],0)+Nz([Qty2],0)+Nz([Qty3],0))
 
Back
Top