IIf statement syntax in calculated field

G

Guest

I have one half of the result I'm looking for in a calculated field in a
query. Here's the half that works:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y")

What I want to add is the value when Action is "Disable", which is "N". If I
need to, I can add a false value of "". I've tried IIf...Then, Else IIf..Then
as well as the following -- all of which generated errors.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y"), IIf([LO1]=True And
[Action]="Disable" ,\"N"), ""))
Can someone help get me on the right track? Would sure appreciate it.
 
J

John Spencer

Just add another comma and the "N"

Calclo1: IIf([LO1]=True And [Action]="Enable","Y","N")

The IIF structure is basically
IIF (TheTest,Value to return if true, Value to return if false)

You can nest IIF statements, but that doesn't seem to be a requirement in
this case.
 
G

Guest

Thanks for your response. Well, the problem is that a value of "" in the
control is possible and the value "N" results when the blank is there, as
well as when the Action value is "Disable."

So I think I need a nested statement...?
--
susan


John Spencer said:
Just add another comma and the "N"

Calclo1: IIf([LO1]=True And [Action]="Enable","Y","N")

The IIF structure is basically
IIF (TheTest,Value to return if true, Value to return if false)

You can nest IIF statements, but that doesn't seem to be a requirement in
this case.

Susan L said:
I have one half of the result I'm looking for in a calculated field in a
query. Here's the half that works:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y")

What I want to add is the value when Action is "Disable", which is "N". If
I
need to, I can add a false value of "". I've tried IIf...Then, Else
IIf..Then
as well as the following -- all of which generated errors.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y"), IIf([LO1]=True And
[Action]="Disable" ,\"N"), ""))
Can someone help get me on the right track? Would sure appreciate it.
Click to expand...
Click to expand...
 
G

Guest

Hi, Lynn. I tried what you suggested and now get an error "Too many arguments."
--
susan


Lynn Trapp said:
Try this instead:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y", IIf([LO1]=True And
[Action]="Disable" ,"N"), "")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Susan L said:
I have one half of the result I'm looking for in a calculated field in a
query. Here's the half that works:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y")

What I want to add is the value when Action is "Disable", which is "N". If
I
need to, I can add a false value of "". I've tried IIf...Then, Else
IIf..Then
as well as the following -- all of which generated errors.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y"), IIf([LO1]=True And
[Action]="Disable" ,\"N"), ""))
Can someone help get me on the right track? Would sure appreciate it.
Click to expand...
Click to expand...
 
G

Guest

Actually the message is "expression you entered has a function with the wrong
number of arguments."
--
susan


Lynn Trapp said:
Try this instead:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y", IIf([LO1]=True And
[Action]="Disable" ,"N"), "")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Susan L said:
I have one half of the result I'm looking for in a calculated field in a
query. Here's the half that works:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y")

What I want to add is the value when Action is "Disable", which is "N". If
I
need to, I can add a false value of "". I've tried IIf...Then, Else
IIf..Then
as well as the following -- all of which generated errors.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y"), IIf([LO1]=True And
[Action]="Disable" ,\"N"), ""))
Can someone help get me on the right track? Would sure appreciate it.
Click to expand...
Click to expand...
 
L

Lynn Trapp

I think I got the parens in the wrong spot. Give this a shot.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y", IIf([LO1]=True And
[Action]="Disable" ,"N", ""))


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Susan L said:
Actually the message is "expression you entered has a function with the
wrong
number of arguments."
--
susan


Lynn Trapp said:
Try this instead:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y", IIf([LO1]=True And
[Action]="Disable" ,"N"), "")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Susan L said:
I have one half of the result I'm looking for in a calculated field in a
query. Here's the half that works:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y")

What I want to add is the value when Action is "Disable", which is "N".
If
I
need to, I can add a false value of "". I've tried IIf...Then, Else
IIf..Then
as well as the following -- all of which generated errors.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y"), IIf([LO1]=True And
[Action]="Disable" ,\"N"), ""))
Can someone help get me on the right track? Would sure appreciate it.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That did the trick! I think I had a typo in my statement. Thank you every so
much!
--
susan


Lynn Trapp said:
I think I got the parens in the wrong spot. Give this a shot.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y", IIf([LO1]=True And
[Action]="Disable" ,"N", ""))


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Susan L said:
Actually the message is "expression you entered has a function with the
wrong
number of arguments."
--
susan


Lynn Trapp said:
Try this instead:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y", IIf([LO1]=True And
[Action]="Disable" ,"N"), "")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I have one half of the result I'm looking for in a calculated field in a
query. Here's the half that works:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y")

What I want to add is the value when Action is "Disable", which is "N".
If
I
need to, I can add a false value of "". I've tried IIf...Then, Else
IIf..Then
as well as the following -- all of which generated errors.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y"), IIf([LO1]=True And
[Action]="Disable" ,\"N"), ""))
Can someone help get me on the right track? Would sure appreciate it.
Click to expand...
Click to expand...
Click to expand...
 
L

Lynn Trapp

You're very welcome, Susan.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Susan L said:
That did the trick! I think I had a typo in my statement. Thank you every
so
much!
--
susan


Lynn Trapp said:
I think I got the parens in the wrong spot. Give this a shot.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y", IIf([LO1]=True And
[Action]="Disable" ,"N", ""))


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Susan L said:
Actually the message is "expression you entered has a function with the
wrong
number of arguments."
--
susan


:

Try this instead:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y", IIf([LO1]=True And
[Action]="Disable" ,"N"), "")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I have one half of the result I'm looking for in a calculated field
in a
query. Here's the half that works:

Calclo1: IIf([LO1]=True And [Action]="Enable","Y")

What I want to add is the value when Action is "Disable", which is
"N".
If
I
need to, I can add a false value of "". I've tried IIf...Then, Else
IIf..Then
as well as the following -- all of which generated errors.

Calclo1: IIf([LO1]=True And [Action]="Enable","Y"), IIf([LO1]=True
And
[Action]="Disable" ,\"N"), ""))
Can someone help get me on the right track? Would sure appreciate
it.
Click to expand...
Click to expand...
Click to expand...
 

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 to extract the latest version of a record 14
Time in calculated field for shifts going past midnight 0
Help needed with IIF in my query 14
IIF statement in query calculated field 5
IIf Statement Error 3
ignoring the falsepart in IIF Function 1
ACCESS - how do I apply a IIF = statement without parameter value prompt. The two column comparison is count 1
IIF statement in Query Criteria 2

Top