Replacing null value with zero in text box

[hedgracer]

I have a text box (shows result of a query) which factors into a sum later on in a report. This text box sometimes has a null value in it which in turn throws an error in the sum field. I need to set this text box to zero if the result is null. I have tried the following:

1. =iif(IsNull([AcctNum]),0,[AcctNum])
2. Nz([AcctNum],0)
3. Nz([AcctNum])

AcctNum is a decimal field. None of these have worked. They all result in a circular reference(?) or #error. Can someone tell me what I am doing wrong? I have googled this thing to death. Thanks for all help in advance.

Dave

 

[dhookom]

You will get a circular error if you don't change the name of the text box. It can't be "AcctNum". The expression must begin with "=".

Try:

=Nz(AcctNum,0)

You can also set the format property to display a 0 when null.

I am wondering why you are summing a field named [AcctNum].

I think summing a null field should not create an error. I expect something else is the issue.

Duane
Hook'D on Access
MS Access MVP