CODEWITHSUNDEEP
ms-access
Isaac Reefman
Isaac Reefman

Reputation: 597

Only add if not null

I'm trying to set a text box in a form to display the total of a range of other text boxes. If there are no values, I want the box empty, but any value needs to display, even if only one of the contributing sources has a value.

Lets say there are three contributing boxes: txtScore1, txtScore2 and txtScore3, and the sum box txtTotal. Before data appears in any of the score boxes, they contain a null value. So I've tried setting txtTotal's control source to:

=Iif(Not IsNull([txtScore1]),[txtScore1])+
Iif(Not IsNull([txtScore2]),[txtScore2])+
Iif(Not IsNull([txtScore3]),[txtScore3])

But this only gives a value if all three have values, as anything + Null = Null. If I add a FalsePart of 0 to them, the value in txtTotal is always 0, even if all three are empty. Do I have to add a Iif(IsNull([txtScore1]) and IsNull([txtScore2]) and IsNull([txtScore3]),null,... before this whole thing, or is there a better way?

Upvotes: 1

Views: 3384

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

The NZ function can handle this situation, assuming you can use it:

=Iif(IsNull([txtScore1]) AND IsNull([txtScore2]) AND IsNull([txtScore3]),
     NULL,
     Nz([txtScore1], 0) + Nz([txtScore2], 0) + Nz([txtScore3], 0))

Nz is the Access version of the standard COALESCE function, which uses the second parameter as a value should the first parameter be NULL. Check the Tech On The Net page for some examples of how to use it.

Upvotes: 1

Related Questions