Sum Function with Blank Values

[niri77]

hello,

I have two columns Column A and Column B. Both column has either a numeric values or blank.

I am trying to sum both columns Column A and Column B, but unable to get values in Sum Column C where either Column A or Column B has blank values.

Can anyone suggest what I should do?

([ColumnA]+[ColumnB])

 

[pbaldy]

For starters, you generally should not store a value that can be calculated from other values. You would just calculate it when you need it on forms/reports. To handle the blanks (Null values), use the Nz() function:

Nz([ColumnA],0) + Nz([ColumnB],0)

 

[Mike375]

A bit of a long way but will help you learn.

Make a query based on your table and drag Column A and Column B to the field part of the Query Design grid. It is the top row.

Then use the IIF function for two new fields.....you type the field name and follow it with a colon.....NewFieldName: and the field is based on the IIF function.

With the IIF function you can convert "the blanks" to a zero, that is, 0

In Access "blank" is called Null.

Your IIF expression could like this:

NewFieldName:IIF([Column A] Is Null,0,[Column A])

The new field will replicate the field Column A except it will have a 0 where Column A has blanks/null.

Do the same for the field Column B and then add the two new fields.

Hope that gets you started.

 

[niri77]

Thank you !

It worked perfectly fine.