IIF function with text field (Access 2000)

Topic

New Reply

I’m trying to use the IIF function with a text field. =IIF([register]![patient_sex]=”F”,”Female”,”Male”) in a report using the expression builder. The field in the table is a text field with length of 1. All I get on the report is #Error. Using Win98SE with Office 2000 Pro. Any help will be appreciated.

Reply | Quote

Replies

Is this formula =IIF([register]![patient_sex]=”F”,”Female”,”Male”) in an unbound text box?

Reply | Quote

Yes, it is.

Reply | Quote

As a test on a report I placed two Fields Gender (bound to the query field) and GenderName(unbound).
The Control Source for GenderName is:
=IIf([Gender]=”F”,”Female”,”Male”).
The GenderName textbox correctly displays the text. You can set the visible property of the bound field to No.
Rupert’s reply is important because I think that you need both the bound control and the unbound control for this to work.

Reply | Quote

Why not set the formula in the underlaying query instead of the report ?

Reply | Quote

Take out the reference to [register]!, whether that is the report or the underlying recordset. In either case, you only reference the field or the control, not the container. Is [patient_sex] both the name of a control and the name of a field? That can cause circular references and result in an Error as well.

Reply | Quote

You are absolutely right. Carelessness on my part. I was using the same name for the control and the field. Thanks for jogging my memory.

Reply | Quote