Add Column Values to get a total in a Query

I have a Query with 5 insurance amounts ( Cancer, Accidental, Group, Short Term, and Sick).  I am wanting to create another column that adds all 5 of these and put the total in that column.  I know you can do it with the expression-
Total : [Cancer_Ammount]+[Accident_Ammount]+[ShortTermDis_Ammount]+[PersonalSick_Ammount]+[GroupTerm_Ammount]

The expression works only if all 5 columns have values.  Not everyone has all of these so some columns contain Null values.  I think that is were my problem is.  The expression will not add a Null value and thus will leave no information in the Total column it creates.
thirdrockitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:


Total : nz([Cancer_Ammount],0)+nz([Accident_Ammount],0)+nz([ShortTermDis_Ammount],0)+nz([PersonalSick_Ammount],0)+nz([GroupTerm_Ammount],0)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aanymgCommented:
First method is to stop worrying about NULL's
- go to the design mode of the table
- set default value of each field to "0" (as all the fields are numeric fields, 0 will cause no problems)
By doing the above, your query will work without any problems. Because, if there is no value for particular field, then by default it will be taken as 0. So no questions of NULL.

Second method (just assisting the answer of capricorn1) use Nz function.
In Access, the Nz function lets you return a value when a variant is null.

The syntax for the Nz function is:  Nz ( variant, [ value_if_null ] )
variant is a variable that is a variant datatype.
value_if_null is optional. It is the value to use when the variant is a null value. If this parameter is omitted and the variant is a null value, the Nz function will return a zero or a zero-length string.

I tried the following by creating Table1 and making all fields numeric fields
SELECT Table1.a, Table1.b, Table1.c, Table1.d, Table1.e,
Nz(Table1.a,0) + Nz(Table1.b,0) + Nz(Table1.c,0) + Nz(Table1.d,0) + Nz(Table1.e,0) as Total
FROM Table1;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.