IIf statement based on multiple fields 1

[mike1971]

Hi all,

I'm having a hell of a time trying to return the correct value on a double iif statement. The SQL is as follows and I think it should work, any ideas????

IIf(([FIELD1]="VARIABLE1&quot,(IIf([FIELD2]="*VARIABLE2*",[RETURN1])),[RETURN2])

I've also tried variations using the AND statement as follows:

IIf(([FIELD1]="VARIABLE1&quotAND([FIELD2]="*VARIABLE2*",[RETURN1])),[RETURN2])

At a complete loss and any assistance GREATLY appreciated.

Cheers
Mike

remember...every step is a step forward..

 

[scottsanpedro]

Are you not getting the incorrect answer back or is the syntax not right??

Should be

=IIF([Cntrl1] = "10",[Field1],IIF([Cntrl1] = "20",[Field2],[Field3]))

so do your statement as follows
If value = something then do something, if value = something then do something,else do this

hope that makes sense

Scott

 

[mike1971]

Thanks for the info but its actually a calculation based on two seperate variables,

Basically, if the value in field 1 = A

and the value in field 2 = B

then return the value in field 2, or use a "Text" value..

if not then simply return the value in field 1

i know i've done this before, but for some reason not working now.

 

[r937]

if the value in field 1 = A
and the value in field 2 = B
then return the value in field 2
if not then simply return the value in field 1

becomes

iif ( field1='A'
and field2='B'
, field2
, field1 )

rudy

 

[mike1971]

Thanks Rudy, I had tried this originally but no luck.. Decided to use a lookup table instead..

Thanks for the help.