Calculated Control

  • Thread starter Thread starter Baz
  • Start date Start date
B

Baz

Hi there

I have a calculated control that needs to add the total of 6 fields

Child1
Child2
Child3
Child4
Child5
Child6

=[Child1]+[Child2]+[Child3]+[Child4]+[Child5]+[Child6]

The problem is that it will only give me a value once all 6 fields have a
value, which is not always the case.

Any suggestions


Thanks

Barry
 
Wrap each one with the NZ function so that the nulls are converted to zeros:

=nz([Child1])+nz([Child2]) . . .
 
Use Nz() to specify what value you want to use if the control is Null, e.g.:

=Nz([Child1],0) + Nz([Child2],0) + Nz([Child3],0) + Nz([Child4],0) +
Nz([Child5],0) + Nz([Child6],0)
 
Finally figured it out:

=Nz([Child1],0)+Nz([Child2],0)+Nz([Child3],0)+Nz([Child4],0)+Nz([Child5],0)+
Nz([Child6],0)

From Access Help:

When you use an arithmetic operator (+, -, *, /) in an expression and the
value of one of the fields in the expression is Null, the result of the
entire expression will be Null. If some records in one of the fields you
used in the expression might have a Null value, you can convert the Null
value to zero using the Nz function.
 
Baz said:
Hi there

I have a calculated control that needs to add the total of 6 fields

Child1
Child2
Child3
Child4
Child5
Child6

=[Child1]+[Child2]+[Child3]+[Child4]+[Child5]+[Child6]

The problem is that it will only give me a value once all 6 fields have a
value, which is not always the case.

Any suggestions
Click to expand...

Summing six fields usually means there is some relation between them.
Chances are that if this was a properly normalized database those numbers
would be in a separate table.
This makes summing them easier and you don't have to change everything when
you get to child7 or 8 or 12
 
Back
Top