How to Add "Properly" in Query

[duckster]

I'm sure it's a simple fix, but I've been trying to figure it out for the last hour here...

Basically, I'm creating an expression in a query that adds the numbers in 10 fields from a table to give the total.

I.e.: Expression: [Amount1]+[Amount2]+[Amount3]...+[Amount10]

The [Amount1], [Amount2], etc. fields are all Currency fields.

There are actually 10 "Amount" fields in the underlying table, with some of the "Amount" fields that have currency values, and some that are null.

When I run the query, I get a blank in the output field for this expression. Not sure why. I did a bit of testing and noticed the only time the query actually adds Amount1 through Amount10 is when ALL AMOUNT fields have a value (other than zero). Even when I put a default of "0" in the underlying table for the "Amount" fields, I still get a blank output if only some Amount fields have a value.

Does anybody know how to fix this? I want the the expression to add all the amounts, and if there are only a few amounts entered, just to add those amounts....

Thanks!

 

[Ron_dK]

You may thry this :

Expression : Nz([Amount1])+Nz([Amount2])....+Nz([Amount10])

 

[RuralGuy]

The default returned value for Nz() is a space (" ") so you will need to alter rak's solution to:
Expression : Nz([Amount1],0)+Nz([Amount2],0)...+Nz([Amount10],0)

 

[EMP]

The default returned value for Nz() is a space (" ") so you will need to alter rak's solution to:
Expression : Nz([Amount1],0)+Nz([Amount2],0)...+Nz([Amount10],0)

rak's expression should work.


When [Amount1] is null and [Amount2] is null,
Nz([Amount1]) and Nz([Amount2]), when exist alone, both will return a zero-length text string.


However, when involved in calculations, they will result in numeric zeros e.g.

Nz([Amount1]) + 0 = 0
Nz([Amount2]) + 0 = 0

Nz([Amount1]) + Nz([Amount2]) = 0

^

 

[RuralGuy]

Hi EMP,
I haven't tested it but you are probably correct. I was simply going with this statement in the VBA help system.

"When used in a query expression, the NZ function should always include the valueifnull argument"

 

[EMP]

"When used in a query expression, the NZ function should always include the valueifnull argument"

The statement refers to situations where
Nz(variant) exists alone
so as to avoid a zero-length string, which is invisible.

^

 

[duckster]

hey thanks, u guys are a lot of help. It adds now, which is great; however, although the "Amount" fields are all "currency" types, the expression produces just numbers withought the "$" sign.

 

[Jon K]

You can use the CCur() function to convert into currency.

Total : CCur(Nz([Amount1])+Nz([Amouint2])....+Nz([Amount10]))
.