What's wrong with my IIF Statement?

[MB]

I'm using this expression in my query and it works great:

PressureScore:
IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",IIf([Pressure]<=140,"6",IIf([Pressure]>=141,"8")))))

However, the following is not working:

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",IIf([Pipe
Material]="Ductile","4",IIf([Pipe Mterial]="PVC" or "HDPE,"2","0"))

Should return scores as:

Cast Iron = 6
Ductile = 4
PVC = 2
HDPE = 2

Using Access 2007.

 

[John Spencer]

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",
IIf([Pipe Material]="Ductile","4",
IIf([Pipe Material]="PVC" or [Pipe Material] ="HDPE","2","0")))

--Insufficient closing parens as posted
--Bad name as posted (Pipe Mterial)
--Missing closing quotes after HDPE
--Must use full comparison expressions. You can't short cut having values on
both sides of the comparison operator.



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

 

[Jerry Whittle]

="PVC" or "HDPE"

The 'or' won't work. You need to nest another IIf statement for HDPE.

If you have to dig this hole any deeper, you may want to consider writing a
Case statement instead. Nexting multiple IIf statements get inefficient and
hard to read when things go wrong.

 

[Bob Barrows [MVP]]

I'm using this expression in my query and it works great:

PressureScore:

IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",I

However, the following is not working:

What does "not working" mean? Incorrect results? Error message? Computer
crashes?
Please try to describe you symptoms without using generic terms like
"not working".

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",IIf([Pipe
Material]="Ductile","4",IIf([Pipe Mterial]="PVC" or "HDPE,"2","0"))

Should return scores as:

Cast Iron = 6
Ductile = 4
PVC = 2
HDPE = 2

Using Access 2007.

Hmm, I don't believe there are enough closing parentheses: count them
for yourself.

 

[MB]

Thank you so much. Lesson learned!
--
MB

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",
IIf([Pipe Material]="Ductile","4",
IIf([Pipe Material]="PVC" or [Pipe Material] ="HDPE","2","0")))

--Insufficient closing parens as posted
--Bad name as posted (Pipe Mterial)
--Missing closing quotes after HDPE
--Must use full comparison expressions. You can't short cut having values on
both sides of the comparison operator.



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

I'm using this expression in my query and it works great:

PressureScore:
IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",IIf([Pressure]<=140,"6",IIf([Pressure]>=141,"8")))))

However, the following is not working:

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",IIf([Pipe
Material]="Ductile","4",IIf([Pipe Mterial]="PVC" or "HDPE,"2","0"))

Should return scores as:

Cast Iron = 6
Ductile = 4
PVC = 2
HDPE = 2

Using Access 2007.

 

[MB]

Sorry about that. I get #Error. I changed my field name to contain no
spaces (PipeMaerial). I ran only one Iif:

PipeMaterialScore: IIf([PipeMaterial]="PVC","6","0")

to return a 6 for PVC and 0 for all others. Still get #Error message.
--
MB

I'm using this expression in my query and it works great:

PressureScore:

IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",I

However, the following is not working:

What does "not working" mean? Incorrect results? Error message? Computer
crashes?
Please try to describe you symptoms without using generic terms like
"not working".

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",IIf([Pipe
Material]="Ductile","4",IIf([Pipe Mterial]="PVC" or "HDPE,"2","0"))

Should return scores as:

Cast Iron = 6
Ductile = 4
PVC = 2
HDPE = 2

Using Access 2007.

Hmm, I don't believe there are enough closing parentheses: count them
for yourself.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

I see nothing wrong with what you typed there, but then again, I missed
that "" or "" goof in your previous post lol
... perhaps you corrected a typo when you typed your post?

Sorry about that. I get #Error. I changed my field name to contain
no spaces (PipeMaerial). I ran only one Iif:

PipeMaterialScore: IIf([PipeMaterial]="PVC","6","0")

to return a 6 for PVC and 0 for all others. Still get #Error
message. --
MB

I'm using this expression in my query and it works great:

PressureScore:

IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",I

However, the following is not working:

What does "not working" mean? Incorrect results? Error message?
Computer crashes?
Please try to describe you symptoms without using generic terms like
"not working".

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",IIf([Pipe
Material]="Ductile","4",IIf([Pipe Mterial]="PVC" or "HDPE,"2","0"))

Should return scores as:

Cast Iron = 6
Ductile = 4
PVC = 2
HDPE = 2

Using Access 2007.

Hmm, I don't believe there are enough closing parentheses: count them
for yourself.

 

[MB]

Yes, it was just a typo in my post. Double-checked my query and table and no
typo there. Still getting #Error.
--
MB

I see nothing wrong with what you typed there, but then again, I missed
that "" or "" goof in your previous post lol
... perhaps you corrected a typo when you typed your post?

Sorry about that. I get #Error. I changed my field name to contain
no spaces (PipeMaerial). I ran only one Iif:

PipeMaterialScore: IIf([PipeMaterial]="PVC","6","0")

to return a 6 for PVC and 0 for all others. Still get #Error
message. --
MB

MB wrote:
I'm using this expression in my query and it works great:

PressureScore:

IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",I
If([Pressure]<=140,"6",IIf([Pressure]>=141,"8")))))

However, the following is not working:

What does "not working" mean? Incorrect results? Error message?
Computer crashes?
Please try to describe you symptoms without using generic terms like
"not working".

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",IIf([Pipe
Material]="Ductile","4",IIf([Pipe Mterial]="PVC" or "HDPE,"2","0"))

Should return scores as:

Cast Iron = 6
Ductile = 4
PVC = 2
HDPE = 2

Using Access 2007.


Hmm, I don't believe there are enough closing parentheses: count them
for yourself.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Well, I just added a field called PipeMaterial to one of my test tables,
entered some values, created a new query, copied and pasted the
PipeMaterialScore: IIf([PipeMaterial]="PVC","6","0") expression from
your post and ran it with the expected results. I'm at a loss to explain
your #Error ... sorry.

Yes, it was just a typo in my post. Double-checked my query and
table and no typo there. Still getting #Error.
--
MB

I see nothing wrong with what you typed there, but then again, I
missed that "" or "" goof in your previous post lol
... perhaps you corrected a typo when you typed your post?

Sorry about that. I get #Error. I changed my field name to contain
no spaces (PipeMaerial). I ran only one Iif:

PipeMaterialScore: IIf([PipeMaterial]="PVC","6","0")

to return a 6 for PVC and 0 for all others. Still get #Error
message. --
MB


:

MB wrote:
I'm using this expression in my query and it works great:

PressureScore:

IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",I

If([Pressure]<=140,"6",IIf([Pressure]>=141,"8")))))

However, the following is not working:

What does "not working" mean? Incorrect results? Error message?
Computer crashes?
Please try to describe you symptoms without using generic terms
like "not working".

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",IIf([Pipe
Material]="Ductile","4",IIf([Pipe Mterial]="PVC" or
"HDPE,"2","0"))

Should return scores as:

Cast Iron = 6
Ductile = 4
PVC = 2
HDPE = 2

Using Access 2007.


Hmm, I don't believe there are enough closing parentheses: count
them for yourself.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[MB]

Thank you so much, Bob, for taking the time with my problem. I appreciate
your testing it, too. I'll go back to the drawing board. :0)
--
MB

Well, I just added a field called PipeMaterial to one of my test tables,
entered some values, created a new query, copied and pasted the
PipeMaterialScore: IIf([PipeMaterial]="PVC","6","0") expression from
your post and ran it with the expected results. I'm at a loss to explain
your #Error ... sorry.

Yes, it was just a typo in my post. Double-checked my query and
table and no typo there. Still getting #Error.
--
MB

I see nothing wrong with what you typed there, but then again, I
missed that "" or "" goof in your previous post lol
... perhaps you corrected a typo when you typed your post?

MB wrote:
Sorry about that. I get #Error. I changed my field name to contain
no spaces (PipeMaerial). I ran only one Iif:

PipeMaterialScore: IIf([PipeMaterial]="PVC","6","0")

to return a 6 for PVC and 0 for all others. Still get #Error
message. --
MB


:

MB wrote:
I'm using this expression in my query and it works great:

PressureScore:


IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",I
If([Pressure]<=140,"6",IIf([Pressure]>=141,"8")))))

However, the following is not working:

What does "not working" mean? Incorrect results? Error message?
Computer crashes?
Please try to describe you symptoms without using generic terms
like "not working".

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",IIf([Pipe
Material]="Ductile","4",IIf([Pipe Mterial]="PVC" or
"HDPE,"2","0"))

Should return scores as:

Cast Iron = 6
Ductile = 4
PVC = 2
HDPE = 2

Using Access 2007.


Hmm, I don't believe there are enough closing parentheses: count
them for yourself.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[raskew via AccessMonster.com]

You might also consider the Switch() function. Saves the problem of having
to match multiple parenthesis.

PressureScore: Switch([Pressure]<=80, 0,[Pressure]<=100, 2,[Pressure]<=120, 4,
[Pressure]<=140, 6, True, 8)

PipeMaterialScore: Switch([Pipe Material]="cast iron", 6,[Pipe Material]
="Ductile", 4,
[Pipe Material]="PVC" or [Pipe Material]="HDPE", 2,True, 0)

Bob

I'm using this expression in my query and it works great:

PressureScore:
IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",IIf([Pressure]<=140,"6",IIf([Pressure]>=141,"8")))))

However, the following is not working:

PipeMaterialScore: IIf([Pipe Material]="cast iron","6",IIf([Pipe
Material]="Ductile","4",IIf([Pipe Mterial]="PVC" or "HDPE,"2","0"))

Should return scores as:

Cast Iron = 6
Ductile = 4
PVC = 2
HDPE = 2

Using Access 2007.