Nz Function

[sbooth]

I have a form with 4 tabs. The 3rd tab is has a subform in datasheet view that lists payments. In the footer of the subform I have Text Box TotalPaid with control source:

=nz(Sum([PaymentAmount]))

On the form I have Text Box TotalPayments with control source:

=(nz([frmEntryPayment_Subform].Form!TotalPaid))

This works fine unless there are no records in the subform in which case it is blank instead of zero.

What am I missing?

 

[missinglinq]

Nz() is one of those quirky Access functions! You can't always depend on the default ValueIfNull being displayed! Try actually assigning zero to it:

=nz(Sum([PaymentAmount]), 0)

 

[sbooth]

Thank you. That worked.

 

[boblarson]

amazing that it worked because the NZ should be like this (INSIDE the Sum):

=Sum(Nz([PaymentAmount],0))

 

[gemma-the-husky]

=nz(Sum([PaymentAmount]), 0)



=Sum(Nz([PaymentAmount],0))


bob

would the first version get this wrong
ie 4+2+null = null, therefore nz function make it 0

whereas second version becomes
4+2+null = 4+2+0 = 6

 

[namliam]

Gemma,

No ... as far as I know both versions will return 6

However...
=NZ(Avg(),0) will give 3 (4+2) / 2
while
=Avg(nz(,0)) will give 2 ( 4 + 2 + 0) / 3

Also
Sum(NZ(,0)) on an empty table/query will return No rows / Null
where
NZ(Sum(),0) on the same empty table/query will return 0

Nice little differences which one can play with