Returning zero when null 2

[dixxy]

Hello,

I am having a hard time understanding something. I have looked and read a hole bunch of threads on "null", "nz", and "IIF". I am just not getting it.

I have areport that is based on a query, this query will return nothing sometimes, in other words it will have no records at all. One of the fields on the report is a total, this is based on the total feild in the query.

This is what I am trying in terms of code. I put this in the control source of my total field:

=IIf(IsNull([total]),0,[total])

This give me #error, were I would like to have 0

This gives me the same error:

=nz([total],0)

Could anyone clear away the smoke here for me?

 

[CosmoKramer]

Access won't let you use functions like Nz on bound controls. Try adding an unbound text box (say, txtTotal), and set as its control source your last attempt from above:

=nz([total],0)

Hoc nomen meum verum non est.

 

[dixxy]

Thanks Cosmo

I was thinking about that after I posted the question...

Found a way...thanks again.

 

[dhookom]

If there are no records in the report, then almost any expression you use in a header or footer that references a field in the record source will result in error.

You could use
=IIf([HasData],[YourExpression],0)

Duane
MS Access MVP

 

[dixxy]

Thanks Duance, but again this has long as the text feild is unbound...Right?

 

[dhookom]

The text field is bound to the expression. The control source of the text box is:
=IIf([HasData],[YourExpression],0)
YourExpression could be [Total] or Sum([Total]) or whatever.

Another gotcha is when the name of the text box is the same as one of the field names.

Duane
MS Access MVP