Null Values

[Barry]

Hi

I am trying to run this code for a calculated field, it works fine as long
as there are no null values, which there are. I am unsure of how to use the
Nz function. If the ChildContibuion is null, this causes the calculated
field's result to be null.

Private Sub TotalContribution_Exit(Cancel As Integer)

Me.TotalContribution = Me.Child1Contribution + Me.Child2Contribution +
Me.Child3Contribution + Me.Child4Contribution + Me.Child5Contribution +
Me.Child6Contribution

End Sub





Can someone please help or point me in the right direction



Thanks



Barry

 

[ak]

Use NZ([YourFieldName],0) for each field

 

[Sandra Daigle]

me.totalcontribution =
nz(me.child1contribution,0)+nz(me.child2contribution,0) . . .

One other thing to consider is that your data is not properly normalized
when you have fields like child1contribution through childnContribution.
Instead, these values should really be in a separate table which is linked
to this table by the primary key field(s). This is preferable for many
reasons but in this case the most compelling might be maintainability. What
happens when someone has 7 children? You have to add the fields to the
table, the form and the expression. In a normalized situation it doesn't
matter - new rows are added as needed without involving you the developer.
The new table might be as simple as:

Pkid (whatever makes sense here)
ChildNum
Contribution

This data would be displayed in a subform which is linked to the mainform on
the pk field(s). Then it is a simple matter to have a sum of the values in
the Contribution field.

 

[Barry]

Thanks, it worked.

I know the table is not the greatest, I sort of inherited it, the whole
thing is a mess, I'll fix it bit by bit.

Thanks

me.totalcontribution =
nz(me.child1contribution,0)+nz(me.child2contribution,0) . . .

One other thing to consider is that your data is not properly normalized
when you have fields like child1contribution through childnContribution.
Instead, these values should really be in a separate table which is linked
to this table by the primary key field(s). This is preferable for many
reasons but in this case the most compelling might be maintainability. What
happens when someone has 7 children? You have to add the fields to the
table, the form and the expression. In a normalized situation it doesn't
matter - new rows are added as needed without involving you the developer.
The new table might be as simple as:

Pkid (whatever makes sense here)
ChildNum
Contribution

This data would be displayed in a subform which is linked to the mainform on
the pk field(s). Then it is a simple matter to have a sum of the values in
the Contribution field.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi

I am trying to run this code for a calculated field, it works fine as
long as there are no null values, which there are. I am unsure of how
to use the Nz function. If the ChildContibuion is null, this causes
the calculated field's result to be null.

Private Sub TotalContribution_Exit(Cancel As Integer)

Me.TotalContribution = Me.Child1Contribution + Me.Child2Contribution +
Me.Child3Contribution + Me.Child4Contribution + Me.Child5Contribution
+ Me.Child6Contribution

End Sub





Can someone please help or point me in the right direction



Thanks



Barry