IIF Statement, Check for Empty Field

[Guest]

I think this is an easy problem, but I just cann't get it right.

I have a report, one of the fields is called Consideration (data
type=currency, no default values, input masks, etc.)

If there is nothing in the field, I want it to show $0.00. If there is a
value, I want it to print that value.

So I tried this: =IIf(IsNull([Consideration]), "$0.00", [Consideration])

But all it prints is '#error'.

Your help is appreciated.

 

[fredg]

I think this is an easy problem, but I just cann't get it right.

I have a report, one of the fields is called Consideration (data
type=currency, no default values, input masks, etc.)

If there is nothing in the field, I want it to show $0.00. If there is a
value, I want it to print that value.

So I tried this: =IIf(IsNull([Consideration]), "$0.00", [Consideration])

But all it prints is '#error'.

Your help is appreciated.

If the control source is an expression, and the name of the control is
the same as the name of a field used in the expression you will get an
#error. Change the control name to something else.

As an alternative to using an expression, use a control bound to the
field.
Set it's Format property to:

$#,##0.00;-$#.##0.00;$0.00;$0.00

See Format property + Number and Currency datatype
in Access help.

 

[Rob Parker]

I suspect that your textbox control is also named "Consideration". Change
its name (eg. to "txtConsideration") and your error should be gone.

Access's behaviour of naming a control with the same name as the underlying
field causes lots of errors. You should, as standard practice, rename
controls so that they never have the same name as the data field.

HTH,

Rob