IIF function always returns true - only on report, not query

[Shea Olmsford]

I have a Yes/No field in a table - call it EXAM. No default value. It is a
combo box with a value list - values Yes;No

I have a form with a subform that has about 12 tabbed controls that refers
to the field with the same drop down values

I have a command button on the form that prints a report to the screen with
the values on the form specially formatted.

I have a control on the report with the string =IIf([Exam],"Yes","No")

No matter what is in the Exam field, the report always prints out YES, even
if the value in the field is NO.

If I copy the above function =IIf([DOT Exam],"Yes","No") to a query and run
it against the table, all is OK - the proper values display.

Why is this happening?

 

[Pendragon]

First, a Yes/No field by default is going to be -1/True/Yes or 0/False/No.
You do not (and should not) need a value list to set Yes/No.

Your control should be Iif([Exam] = -1, "Yes","No).

 

[Marshall Barton]

I have a Yes/No field in a table - call it EXAM. No default value. It is a
combo box with a value list - values Yes;No

I have a form with a subform that has about 12 tabbed controls that refers
to the field with the same drop down values

I have a command button on the form that prints a report to the screen with
the values on the form specially formatted.

I have a control on the report with the string =IIf([Exam],"Yes","No")

No matter what is in the Exam field, the report always prints out YES, even
if the value in the field is NO.

If I copy the above function =IIf([DOT Exam],"Yes","No") to a query and run
it against the table, all is OK - the proper values display.

A Yes/No field can not be a combo box so this isn't making
much sense. If it were a Text field that contained the
words Yes or No, then you would not need the expression.

In an attempt to understand this better add a text box bound
to the Exam field so you can see what values it really has.

 

[Shea Olmsford]

This is Access 2003. When I add a Yes/No field to the table in design view,
the Lookup tab has on its Display control field three options, one of which
is combo box. When I choose it and select value list for the row source
property, then enter Yes;No for the Row source, it accepts it.

I enter data into the Yes/No field simply by choosing Yes/No in the drop
down list the datasheet.

I have a Yes/No field in a table - call it EXAM. No default value. It is a
combo box with a value list - values Yes;No

I have a form with a subform that has about 12 tabbed controls that refers
to the field with the same drop down values

I have a command button on the form that prints a report to the screen with
the values on the form specially formatted.

I have a control on the report with the string =IIf([Exam],"Yes","No")

No matter what is in the Exam field, the report always prints out YES, even
if the value in the field is NO.

If I copy the above function =IIf([DOT Exam],"Yes","No") to a query and run
it against the table, all is OK - the proper values display.

A Yes/No field can not be a combo box so this isn't making
much sense. If it were a Text field that contained the
words Yes or No, then you would not need the expression.

In an attempt to understand this better add a text box bound
to the Exam field so you can see what values it really has.

 

[Marshall Barton]

OK, I take it back. You can have a Value List with Yes;No,
On;Off or True;False. (I wonder what convolutions they had
to go through to get that to work?) They even allow the
items in the list to be in either order (or even mixed) so I
guess they must check for those specific text strings. They
do that in other odd places, so I guess this isn't any more
strange than the others.

The only thing I can think of is that you might have
misspelled the name of the field so the IIf expression is
looking at the wrong (or nonexistent?) field.

Note that you do not need an expression to get the desired
effect. Instead, just bind the text box to the field and
set the text box's Format property to Yes/No
--
Marsh
MVP [MS Access]

This is Access 2003. When I add a Yes/No field to the table in design view,
the Lookup tab has on its Display control field three options, one of which
is combo box. When I choose it and select value list for the row source
property, then enter Yes;No for the Row source, it accepts it.

I enter data into the Yes/No field simply by choosing Yes/No in the drop
down list the datasheet.

I have a Yes/No field in a table - call it EXAM. No default value. It is a
combo box with a value list - values Yes;No

I have a form with a subform that has about 12 tabbed controls that refers
to the field with the same drop down values

I have a command button on the form that prints a report to the screen with
the values on the form specially formatted.

I have a control on the report with the string =IIf([Exam],"Yes","No")

No matter what is in the Exam field, the report always prints out YES, even
if the value in the field is NO.

If I copy the above function =IIf([DOT Exam],"Yes","No") to a query and run
it against the table, all is OK - the proper values display.

A Yes/No field can not be a combo box so this isn't making
much sense. If it were a Text field that contained the
words Yes or No, then you would not need the expression.

In an attempt to understand this better add a text box bound
to the Exam field so you can see what values it really has.

 

[Shea Olmsford]

Thanks. That works great. I did not misspell the name. I assume its just
a quirk of Access. Anyway, the report field works fine now. This is a good
example of over-programming when a far simpler solution will work

OK, I take it back. You can have a Value List with Yes;No,
On;Off or True;False. (I wonder what convolutions they had
to go through to get that to work?) They even allow the
items in the list to be in either order (or even mixed) so I
guess they must check for those specific text strings. They
do that in other odd places, so I guess this isn't any more
strange than the others.

The only thing I can think of is that you might have
misspelled the name of the field so the IIf expression is
looking at the wrong (or nonexistent?) field.

Note that you do not need an expression to get the desired
effect. Instead, just bind the text box to the field and
set the text box's Format property to Yes/No
--
Marsh
MVP [MS Access]

This is Access 2003. When I add a Yes/No field to the table in design view,
the Lookup tab has on its Display control field three options, one of which
is combo box. When I choose it and select value list for the row source
property, then enter Yes;No for the Row source, it accepts it.

I enter data into the Yes/No field simply by choosing Yes/No in the drop
down list the datasheet.

Shea Olmsford <Shea (e-mail address removed)>
wrote:

I have a Yes/No field in a table - call it EXAM. No default value. It is a
combo box with a value list - values Yes;No

I have a form with a subform that has about 12 tabbed controls that refers
to the field with the same drop down values

I have a command button on the form that prints a report to the screen with
the values on the form specially formatted.

I have a control on the report with the string =IIf([Exam],"Yes","No")

No matter what is in the Exam field, the report always prints out YES, even
if the value in the field is NO.

If I copy the above function =IIf([DOT Exam],"Yes","No") to a query and run
it against the table, all is OK - the proper values display.


A Yes/No field can not be a combo box so this isn't making
much sense. If it were a Text field that contained the
words Yes or No, then you would not need the expression.

In an attempt to understand this better add a text box bound
to the Exam field so you can see what values it really has.