Very Simple Iif Question

[andrea]

I am usually quite competent working with Access and the basic programming of
it. But, for some reason, I can't get it to produce the result I want.
Currently I have:
=Iif([Data Costs]=1,"Data Service","Phone Service")

where Data Costs is a Yes/No data type (looks like a checkbox in my table).
The problem is that when I run this, I get "#Error"

I have tried in place of 1 the following options: Yes, No, True, False, 0,
1, -1, On, Off... I have tried all of these with quotes and without quotes
around them. Finally, I have tried =Iif([Data Costs],"Data Service","Phone
Service") which only produced the result of "Data Service" whether or not the
box was checked.

I'm pretty frustrated... someone please help! I greatly appreciate it.

 

[Andy G]

Try

=IIf(([Data Costs] = -1), "Data Service", "Phone Service")

If the Data Cost field is checked (ie. True) then this field will display
"Data Service", if not it will display "Phone Service"

 

[fredg]

I am usually quite competent working with Access and the basic programming of
it. But, for some reason, I can't get it to produce the result I want.
Currently I have:
=Iif([Data Costs]=1,"Data Service","Phone Service")

where Data Costs is a Yes/No data type (looks like a checkbox in my table).
The problem is that when I run this, I get "#Error"

I have tried in place of 1 the following options: Yes, No, True, False, 0,
1, -1, On, Off... I have tried all of these with quotes and without quotes
around them. Finally, I have tried =Iif([Data Costs],"Data Service","Phone
Service") which only produced the result of "Data Service" whether or not the
box was checked.

I'm pretty frustrated... someone please help! I greatly appreciate it.

A check box field's value is either -1 or 0 (NOT 1 or 0)
So:

=Iif([Data Costs]=-1,"Data Service","Phone Service")

If the above also gives an error it is probably because the name of
the control is "Data Costs".
The name of an Access control cannot be the same as the name of a
field used in it's control source expression.

 

[andrea]

Ah, I had tried the -1 before, but I was at the point where I was trying
anything, even 1. What worked is CHANGING THE CONTROL NAME! Ah, this made
all the difference. I hadn't run into this before. Very strange. I renamed
it to "Other" because it was previously named "Data Costs" and used:
=Iif([Data Costs]=-1,"Data Service","Phone Service")

Thank you for your assistance!

 

[Mike Painter]

I am usually quite competent working with Access and the basic
programming of it. But, for some reason, I can't get it to produce
the result I want. Currently I have:
=Iif([Data Costs]=1,"Data Service","Phone Service")

where Data Costs is a Yes/No data type (looks like a checkbox in my
table). The problem is that when I run this, I get "#Error"

I have tried in place of 1 the following options: Yes, No, True,
False, 0, 1, -1, On, Off... I have tried all of these with quotes and
without quotes around them. Finally, I have tried =Iif([Data
Costs],"Data Service","Phone Service") which only produced the result
of "Data Service" whether or not the box was checked.

Both
=Iif([Data Costs]= True,"Data Service","Phone Service")
and
=Iif([Data Costs],"Data Service","Phone Service")

should evaluate to True if the field is a "Yes/no" type and the box is
checked.

I stay away from numbers and anything but True and False.

Is this by chance a field in an option group?

 

[fredg]

Ah, I had tried the -1 before, but I was at the point where I was trying
anything, even 1. What worked is CHANGING THE CONTROL NAME! Ah, this made
all the difference. I hadn't run into this before. Very strange. I renamed
it to "Other" because it was previously named "Data Costs" and used:
=Iif([Data Costs]=-1,"Data Service","Phone Service")

Thank you for your assistance!

When you create a form using the wizard, or add a control to the form
from the Field List tool button, Access assigns the new control with
the same name as the field in it's control source, i.e. [Data Costs]
and "Data Costs".
This does not create any problem.

However, if you change the control source to an expression, as you
did, Access will get confused, and not know whether "=IIf([Data Costs]
= -1" refers to the field in the table or the control on the form. You
get the #Error. Easiest way around future errors is to always use an
unbound control when writing an expression. It's unlikely you will
have a field named "Text1", "Text2", etc., which is what Access will
name new unbound controls.