Access Query Build Problem

[ProgEval]

I am adding fields in a query, but I am running into a problem. I am using the build function to add 5 number fields together. In some of the fields there is a null instead of having a number. When it tries to add a null, it appears that it the total result is null even though there are numbers in the other fields.

Example........
Field1 = 2
Field2 = 3
Field3 = blank/null

In build....
If, Total: [Field1]+[Field2]+[Field3], then Total = blank/null, instead of 5 (desired result)
Although, if Total: [Field1]+[Field2], then Total = 5

Any ideas why these blanks are giving me problems?
Thanks in advance!

 

[PWise]

nz([Field1],0)+nz([Field2],0)+nz([Field3],0)

 

[lespaul]

try:

Total: NZ(Field1, 0) + NZ(Field2, 0) + NZ(field3, 0)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

Summing values across fields rather than across records usually suggests an un-normalized table structure. Leslie's signature includes a helpful link.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JoeAtWork]

Not necessarily, could be something like:

SubTotal + SalesTax + Freight = Total

 

[dhookom]

That's why I stated "usually suggests". I would guess that more than 50% of the time when the original poster describes his/her system, the table structure is un-normalized. For instance I often see fields for date/time intervals such as 5 days of the week. It's a bit difficult to tell when the OP provides only fictitious field names.

There could be a good case for arguing that "SubTotal + SalesTax + Freight" would not be normalized. "SubTotal" suggests a value that can be calculated and probably not stored. "SalesTax" & "Freight" could be stored as separate, related records in a SalesOrderOtherCharges table. I'm not sure I would split them out though.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ProgEval]

That works. Thanks for your help guys!