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

[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.

 

[gadavis1701]

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.

 

[Guest]

Try this --
Total: Sum(Nz([Qty1])+Nz([Qty2])+Nz([Qty3]))

 

[fredg]

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.

Look up the NZ function in VBA help.

Exp:Nz([FieldA]) + Nz([FieldB]) + etc.

 

[gadavis1701]

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?

 

[Guest]

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.

 

[Guest]

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))