MS Access query: A = B+C+D yields zero if either of B, C, D are blank

Member_2_7966563
Member_2_7966563 used Ask the Experts™
on
In MS Access, I have a table linked to an external Excel file. In that table I have three numerical columns. In MS Access I have a query that uses those 3 columns and adds a fourth field which is the sum of all three columns.

Here is my problem: it only works as expected when there is data in all three columns. If even one of the columns has a blank in it, then the sum field I added shows a zero.

How can I fix this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
in Access, any NULL value, used in any numeric expression will result in a NULL

A = NZ(B, 0) + NZ(C, 0) + NZ(D, 0)

Author

Commented:
Thanks a lot Dale. That solved the problem.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
you are welcome! glad I could help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial