IIF IsNull

[Matt Greatorex]

I've been searching the forum for a while, so hopefully I haven't missed the answer to this one. It may sound like a small problem, but it's driving me mad.

I have a series of TextBoxes on a Form.
Two of these are named 'Department' and 'Shift'.
Originally, the ControlSource for the former was 'Department' and for the latter was 'Shift', both from the same source table.
All data was being displayed without any problems (any null values resulting in an empty field).

I altered the ControlSource on each to read:
=IIf(IsNull([Department]),"Unknown",[Department])
and
=IIf(IsNull([Shift]),"Unknown",[Shift])

The Shift field works as I'd hoped it would, but the Department field displays '#Error', whether there is a value or not.

I've tried deleting the problematic TextBox, then recreating it by copying and pasting from one that works (in case there was some setting I was missing). In this case, the only difference between the two is the source field, but given that using the source on it's own doesn't present any problems, I'm at a loss.

This same problem occurs on a small number of other Forms, as well as on certain Reports.

Any thoughts welcome.

 

[supercharge]

Try this instead:

= Nz(Department, "Unknown")

 

[Pat Hartman]

Since the fields were originally bound, the Name property of the field might still be the name of a bound field. Try changing the Name of the control. You do realize that you will no longer be able to update these fields on this form, don't you?

 

[Matt Greatorex]

Supercharge - Tried it, but still get the same error message.

Pat - Tried renaming the field, but still get the same error message.

Thanks for the concern, but these fields are just for the user's information. Any whose contents I might want them to edit are elsewhere on the form.

 

[supercharge]

What are the data types of your fields (Department and Shift) in your table? Can you post a sample database?

 

[Mile-O]

Put two hidden textboxes on your form and bind them to the relevant fields.

Then, with your two presentation textboxes, use your expression replacing the field name with the name of the relevant hidden textbox.

 

[Matt Greatorex]

Thanks a lot for that.

Works a treat on the form. I shall try it out on the reports exhibiting the same symptoms and see what happens.

At the risk of pushing my luck, do you have any idea why this works?