SUM function does not work when some cells are blank in Access2010

[ian87]

Good morning everyone,

I am trying to add fields together in a form into a subtotal textbox. Basically I have figures for [Ireland09], [UK09] but [France09] is an empty field. I need to include france however as when i use my query france sometimes has values in it.

When I try to do a Sum for the European total, adding up the above fields, it returns a blank cell because it will not sum when there are blank cells. I am a novice obviously and would like an expression builder solution that adds everything together but ignores the fields that are blank.

Data:

Ireland09 27
UK09 15
France09 (Blank)

Current formula for European total text box
=[Ireland09]+[UK09]+[France09]


Thanks for any help guys and girls!

Ian

 

[JANR]

Use Nz function to handle Nulls.

=Nz([Ireland09],0)+Nz([UK09],0)+Nz([France09],0)

JR

 

[ian87]

MY MAIN MAN JR!!!

CHEERS BUDDY, it's been annoying me for a while now!

 

[PaulO]

I'll second that ... i can't count the amount of times i've created workarounds to fix this issue ... normally by forcing zeros into query fields or using iif(isnull([field]),etc functionality

 

[mimler]

Hello,

I am having the similiar problem but when I enter the expression

=Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0)

I get an error message that says "The expression Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0) cannot be used in a calculated column.

The fields I am trying to add are Long Integer, Currency. Would that affect it?

Thanks!