Iif not working

N

Noreen

Hi, I'm new here. But this is driving me crazy! What is wrong with this IIF
statement? Sometimes, if I try to use "|" because of errors with a "," and
it works. But then I go back into design, Access does not recognize it. I
change the "|" back to "," and it still tells me it's an invalid character.
And if I am in the "Design" mode, it does not let me go back to SQL.

PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])

What am I doing wrong? Thank you.
 
B

Bob Barrows [MVP]

Noreen said:
Hi, I'm new here. But this is driving me crazy! What is wrong with
this IIF statement? Sometimes, if I try to use "|" because of errors
with a "," and it works.
Click to expand...

Well, first of all, I don't understand what you are doing with that "|"
character ... what gave you the idea to try to use it?
Could you show us the statement where you used it and it "worked"?
But then I go back into design, Access does
not recognize it. I change the "|" back to "," and it still tells
me it's an invalid character. And if I am in the "Design" mode, it
does not let me go back to SQL.

PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])

What am I doing wrong? Thank you.
Click to expand...

I don't see anything wrong with this expression, sorry.
 
K

KARL DEWEY

I think your formulation is not what you intended.
PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])

This is how I see yours working --
If [Prmt_Type] not equal "Building" then check more else [prmt_type]
which will be "Building".
then check more --
If [Prmt_Type] equal "zoning" then "zoning" else [prmt_type]

TRUTH TABLE --
If [prmt_type] -- Results
Building - Building
zoning - zoning
anything - anything
 
K

KARL DEWEY

TRUTH TABLE --
If [prmt_type] -- Results
Building - Building
zoning - zoning
anything - OTHER

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
I think your formulation is not what you intended.
PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])

This is how I see yours working --
If [Prmt_Type] not equal "Building" then check more else [prmt_type]
which will be "Building".
then check more --
If [Prmt_Type] equal "zoning" then "zoning" else [prmt_type]

TRUTH TABLE --
If [prmt_type] -- Results
Building - Building
zoning - zoning
anything - anything

--
KARL DEWEY
Build a little - Test a little


Noreen said:
Hi, I'm new here. But this is driving me crazy! What is wrong with this IIF
statement? Sometimes, if I try to use "|" because of errors with a "," and
it works. But then I go back into design, Access does not recognize it. I
change the "|" back to "," and it still tells me it's an invalid character.
And if I am in the "Design" mode, it does not let me go back to SQL.

PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])

What am I doing wrong? Thank you.
Click to expand...
Click to expand...
 
R

Ron2006

How about writing it this way:


PrmtTypeXY: iif (([Prmt_Type]="Building" OR [Prmt_Type]="zoning"),
[prmt_type],"OTHER")


or the longer format


PrmtTypeXY: iif ([Prmt_Type]="Building",
[prmt_type],iif([Prmt_Type]="zoning",[prmt_type],"OTHER"))


Ron
 
K

KARL DEWEY

If that is what you want to achieve. Either will give you the same as before.

Most folks write a nest IIF statement as Condition to test, Results if true,
((Condition to test, Results if true, Results if not true)).
You wrote it --
Condition to test, ((Condition to test, Results if true, Results if not
true)), Results if true.

Can you show a truth table for what you want as results?
 
L

Lord Kelvan

PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER",[prmt_type]))

you had the ) in the wrong place
with nested iifs you put all closing ) at the end of the statement not
in the middle of it

hope this helps

Regards
Kelvan
 
K

KARL DEWEY

The ) was correct for the syntax used --
IIF(Condition, (condition, true results, false results), false results)
When you moved the ) it became --
IIF(Condition, (condition, true results, false results, false results))
 
N

Noreen

Please forgive me for not getting back sooner, but I was away without a
computer and I wasn't expecting such quick answers. So, thank you one and
all. I will again try this with Ron's answer (what I wanted) and see what
happens.

SQL had given me the error previously and told me to try "|" . Well, it
worked once...

I will let you know how it works out...

Noreen D P
PA - GIS Technician
 
N

Noreen

Yes, this is what I am trying to do...
--
Noreen D P
PA - GIS Technician


KARL DEWEY said:
I think your formulation is not what you intended.
PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])

This is how I see yours working --
If [Prmt_Type] not equal "Building" then check more else [prmt_type]
which will be "Building".
then check more --
If [Prmt_Type] equal "zoning" then "zoning" else [prmt_type]

TRUTH TABLE --
If [prmt_type] -- Results
Building - Building
zoning - zoning
anything - anything

--
KARL DEWEY
Build a little - Test a little


Noreen said:
Hi, I'm new here. But this is driving me crazy! What is wrong with this IIF
statement? Sometimes, if I try to use "|" because of errors with a "," and
it works. But then I go back into design, Access does not recognize it. I
change the "|" back to "," and it still tells me it's an invalid character.
And if I am in the "Design" mode, it does not let me go back to SQL.

PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])

What am I doing wrong? Thank you.
Click to expand...
Click to expand...
 
N

Noreen

Oops, the statement still gave me errors. I didn't notice that Ron's answer
had = "Building" instead of <> "Building". But I tried most answers and
still got the same errors. It's like it's reading a different language or
something, because double quotes were wrong, too.

All those answers still gave me errors! Help!

BTW, how do I show you my results? I can't paste and I can't attach...
--
Noreen D P
PA - GIS Technician

--
Noreen D P
PA - GIS Technician


Noreen said:
Please forgive me for not getting back sooner, but I was away without a
computer and I wasn't expecting such quick answers. So, thank you one and
all. I will again try this with Ron's answer (what I wanted) and see what
happens.

SQL had given me the error previously and told me to try "|" . Well, it
worked once...

I will let you know how it works out...

Noreen D P
PA - GIS Technician


Ron2006 said:
How about writing it this way:


PrmtTypeXY: iif (([Prmt_Type]="Building" OR [Prmt_Type]="zoning"),
[prmt_type],"OTHER")


or the longer format


PrmtTypeXY: iif ([Prmt_Type]="Building",
[prmt_type],iif([Prmt_Type]="zoning",[prmt_type],"OTHER"))


Ron
Click to expand...
Click to expand...
 
K

KARL DEWEY

All those answers still gave me errors! Help! BTW, how do I show you my
results?
What does the error say?
 
N

Noreen

It says "The expression you entered contains invalid syntax." Then it says
"You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it with quotation marks." On
this phrase PrmtTypeXY:
IIf([Prmt_Type]<>'Building',(IIf([prmt_type]='zoning',[prmt_type],'OTHER'),[prmt_type])

Noreen D P
PA - GIS Technician
 
N

Noreen

I'm sorry for all the notes, but I realized I was using the wrong field for
the second IIF statement. But I get the same error.
--
Noreen D P
PA - GIS Technician


Noreen said:
It says "The expression you entered contains invalid syntax." Then it says
"You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it with quotation marks." On
this phrase PrmtTypeXY:
IIf([Prmt_Type]<>'Building',(IIf([prmt_type]='zoning',[prmt_type],'OTHER'),[prmt_type])

Noreen D P
PA - GIS Technician


KARL DEWEY said:
results?
What does the error say?
Click to expand...
Click to expand...
 
N

Noreen

Please disregard using the wrong field. I get all flustered sometimes...
--
Noreen D P
PA - GIS Technician


Noreen said:
I'm sorry for all the notes, but I realized I was using the wrong field for
the second IIF statement. But I get the same error.
--
Noreen D P
PA - GIS Technician


Noreen said:
It says "The expression you entered contains invalid syntax." Then it says
"You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it with quotation marks." On
this phrase PrmtTypeXY:
IIf([Prmt_Type]<>'Building',(IIf([prmt_type]='zoning',[prmt_type],'OTHER'),[prmt_type])

Noreen D P
PA - GIS Technician


KARL DEWEY said:
All those answers still gave me errors! Help! BTW, how do I show you my
results?
What does the error say?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Make sure your parentheses match up. You should have the same number of left
and right parentheses. I think you are missing one at the end.

IIf([Prmt_Type]<>'Building',(IIf([prmt_type]='zoning',[prmt_type],'OTHER'),[prmt_type]))

A little trick to make sure you have the correct number (not necessarily in
the correct spot) is to start at the left and add one for each "(" and
subtract one for each ")". IF you get to the end and the result is not zero,
then you need to figure out what is missing. Positive number equates to too
few ")" and a negative number equates to too many ")". Or too many or too few
"(".

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
N

Noreen

Thanks for the trick -- it's very helpful. But my () DO match up...
--
Noreen D P
PA - GIS Technician


John Spencer said:
Make sure your parentheses match up. You should have the same number of left
and right parentheses. I think you are missing one at the end.

IIf([Prmt_Type]<>'Building',(IIf([prmt_type]='zoning',[prmt_type],'OTHER'),[prmt_type]))

A little trick to make sure you have the correct number (not necessarily in
the correct spot) is to start at the left and add one for each "(" and
subtract one for each ")". IF you get to the end and the result is not zero,
then you need to figure out what is missing. Positive number equates to too
few ")" and a negative number equates to too many ")". Or too many or too few
"(".

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I'm sorry for all the notes, but I realized I was using the wrong field for
the second IIF statement. But I get the same error.
Click to expand...
Click to expand...
 
K

KARL DEWEY

Post your query SQL.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

--
KARL DEWEY
Build a little - Test a little


Noreen said:
Thanks for the trick -- it's very helpful. But my () DO match up...
--
Noreen D P
PA - GIS Technician


John Spencer said:
Make sure your parentheses match up. You should have the same number of left
and right parentheses. I think you are missing one at the end.

IIf([Prmt_Type]<>'Building',(IIf([prmt_type]='zoning',[prmt_type],'OTHER'),[prmt_type]))

A little trick to make sure you have the correct number (not necessarily in
the correct spot) is to start at the left and add one for each "(" and
subtract one for each ")". IF you get to the end and the result is not zero,
then you need to figure out what is missing. Positive number equates to too
few ")" and a negative number equates to too many ")". Or too many or too few
"(".

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I'm sorry for all the notes, but I realized I was using the wrong field for
the second IIF statement. But I get the same error.
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Barrows [MVP]

I find it helpful to break out nested statements like so:
IIf
(
[Prmt_Type]<>'Building',
( <---
IIf
(
[prmt_type]='zoning',
[prmt_type],
'OTHER'
),
[prmt_type]
)
.... which makes it pretty obvious that you failed to close the second
parenthesis. There was no reason to start enclosing the entire nested
iif in parentheses. This should work:

IIf
(
[Prmt_Type]<>'Building',
IIf
(
[prmt_type]='zoning',
[prmt_type],
'OTHER'
),
[prmt_type]
)

.... which, after removing the whitespace, translates to this
single-liner:

IIf([Prmt_Type]<>'Building',IIf([prmt_type]='zoning',[prmt_type],'OTHER'
),[prmt_type])
 
N

Noreen

Bob,

Thanks. I was looking at my 1st question on 8/28 instead of one of them
today. At any rate, both give me that error.

And Karl,

Thank you, I know how to do the copy/paste thing you referenced, but I was
referring to a dialog box with the error in it and a copy from hitting the
"Print Screen" and then pasting. (This didn't work)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query design view - commas being changed to pipe characters 3
IIF Expression returning Zero Records in Query 2
IIF STATEMENT 6
Multiple criteria IIF function help 2
joining two iif statements 2
Iif statement 9
IIF Statement not working as expected 3
IIf statement not working 4

Top