Simplify calculation (1 Viewer)

[Humpty]

Hi all.

I was wondering if anyone might be able to help me simplify the following that I am using to calculate a value on one of my forms:

=(Iif (IsNull([txt24Calc]),0,[txt24Calc]))+
(Iif (IsNull([txt27Calc]),0,[txt27Calc]))+
(Iif (IsNull([txt30Calc]),0,[txt30Calc]))+
(Iif (IsNull([txt33Calc]),0,[txt33Calc]))+
(Iif (IsNull([txt36Calc]),0,[txt36Calc]))+
(Iif (IsNull([txt39Calc]),0,[txt39Calc]))+
(Iif (IsNull([txt42Calc]),0,[txt42Calc]))+
(Iif (IsNull([txt45Calc]),0,[txt45Calc]))+
(Iif (IsNull([txt48Calc]),0,[txt48Calc]))+
(Iif (IsNull([txt51Calc]),0,[txt51Calc]))+
(Iif (IsNull([txt54Calc]),0,[txt54Calc]))+
(Iif (IsNull([txt57Calc]),0,[txt57Calc]))+
(Iif (IsNull([txt60Calc]),0,[txt60Calc]))

All the [txt..Calc] are calcutated text boxes themselves. I guess I am just wondering if there is a simple way to ditch the 'IIf IsNull' segments.

Any help appreciated!

Thanks.

 

[phatnq2002]

Hi all.

I was wondering if anyone might be able to help me simplify the following that I am using to calculate a value on one of my forms:

=(Iif (IsNull([txt24Calc]),0,[txt24Calc]))+
(Iif (IsNull([txt27Calc]),0,[txt27Calc]))+
(Iif (IsNull([txt30Calc]),0,[txt30Calc]))+
(Iif (IsNull([txt33Calc]),0,[txt33Calc]))+
(Iif (IsNull([txt36Calc]),0,[txt36Calc]))+
(Iif (IsNull([txt39Calc]),0,[txt39Calc]))+
(Iif (IsNull([txt42Calc]),0,[txt42Calc]))+
(Iif (IsNull([txt45Calc]),0,[txt45Calc]))+
(Iif (IsNull([txt48Calc]),0,[txt48Calc]))+
(Iif (IsNull([txt51Calc]),0,[txt51Calc]))+
(Iif (IsNull([txt54Calc]),0,[txt54Calc]))+
(Iif (IsNull([txt57Calc]),0,[txt57Calc]))+
(Iif (IsNull([txt60Calc]),0,[txt60Calc]))

All the [txt..Calc] are calcutated text boxes themselves. I guess I am just wondering if there is a simple way to ditch the 'IIf IsNull' segments.

Any help appreciated!

Thanks.

That form has a recordsource? and the [txt...Calc] have their controlsource?
If right, you can make a query, then use it as the form's recordsource.
In the query you make a calculate field likes above.

In contrast, you set the default value of each in [txt...Calc] to 0. So you can rewrite:
=[txt24Calc]+[txt27Calc]+...

 

[Humpty]

Thanks for the swift response phatnq2002. The form does have a record source but as mentioned the txt..Calc controls are calculated and the calculation is based on user input (into bound controls on the form). I don't think there is a way to simplify this using a query.

It all works, I was just wondering if it can be simplified somehow

 

[Humpty]

Just remembered the 'Nz' function

That does the job:

=Nz([txt24Calc])+
Nz([txt27Calc])+
Nz([txt30Calc])+
Nz([txt33Calc])+
Nz([txt36Calc])+
Nz([txt39Calc])+
Nz([txt42Calc])+
Nz([txt45Calc])+
Nz([txt48Calc])+
Nz([txt51Calc])+
Nz([txt54Calc])+
Nz([txt57Calc])+
Nz([txt60Calc])

Thanks again for the help.