IIf statement syntax in calculated field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Susan L - 14 Nov 2005 17:47 GMT

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.

Signature

susan

Reply to this Message

John Spencer - 14 Nov 2005 17:55 GMT

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.

>I have one half of the result I'm looking for in a calculated field in a
> query. Here's the half that works:
[quoted text clipped - 10 lines]
> [Action]="Disable" ,\"N"), ""))
> Can someone help get me on the right track? Would sure appreciate it.

Reply to this Message

Susan L - 14 Nov 2005 18:30 GMT

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...?

Signature

susan

> Just add another comma and the "N"
>
[quoted text clipped - 20 lines]
> > [Action]="Disable" ,\"N"), ""))
> > Can someone help get me on the right track? Would sure appreciate it.

Reply to this Message

Lynn Trapp - 14 Nov 2005 17:57 GMT

Try this instead:

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

Signature

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

Reply to this Message

Susan L - 14 Nov 2005 18:41 GMT

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

Signature

susan

> Try this instead:
>
[quoted text clipped - 15 lines]
> > [Action]="Disable" ,\"N"), ""))
> > Can someone help get me on the right track? Would sure appreciate it.

Reply to this Message

Susan L - 14 Nov 2005 18:43 GMT

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

Signature

susan

> Try this instead:
>
[quoted text clipped - 15 lines]
> > [Action]="Disable" ,\"N"), ""))
> > Can someone help get me on the right track? Would sure appreciate it.

Reply to this Message

Lynn Trapp - 14 Nov 2005 19:18 GMT

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", ""))

Signature

> Actually the message is "expression you entered has a function with the
> wrong
[quoted text clipped - 20 lines]
>> > [Action]="Disable" ,\"N"), ""))
>> > Can someone help get me on the right track? Would sure appreciate it.

Reply to this Message

Susan L - 14 Nov 2005 19:34 GMT

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

Signature

susan

> I think I got the parens in the wrong spot. Give this a shot.
>
[quoted text clipped - 25 lines]
> >> > [Action]="Disable" ,\"N"), ""))
> >> > Can someone help get me on the right track? Would sure appreciate it.

Reply to this Message

Lynn Trapp - 14 Nov 2005 19:55 GMT

You're very welcome, Susan.

Signature

> That did the trick! I think I had a typo in my statement. Thank you every
> so
[quoted text clipped - 33 lines]
>> >> > Can someone help get me on the right track? Would sure appreciate
>> >> > it.

Reply to this Message