iif and or not working in Access update query

[DRANDON]

I am trying to get a "Y" or a "N" in an Access query (Flag).

Here is my "update to" formula:

IIf([ADMISSION SVS CD]="EOP" And [I/O]="O","Y","N") Or IIf([ADMISSION SVS CD]="EO2" And [I/O]="O","Y","N")

Inexplicably I get "-1" for all records as a result. What is wrong with it?

Thank you so much in advance.

 

[DRANDON]

I figured it out.

IIf([ADMISSION SVS CD]="EOP" Or [ADMISSION SVS CD]="EO2" And [I/O]="O","Y","N")

 

[Joe4]

Try like this:

[COLOR=#333333]IIf((([ADMISSION SVS CD]="EOP" Or [ADMISSION SVS CD]="EO2") And [I/O]="O"),"Y","N")[/COLOR]

 

[Joe4]

I figured it out.

IIf([ADMISSION SVS CD]="EOP" Or [ADMISSION SVS CD]="EO2" And [I/O]="O","Y","N")

Though not necessary, I would recommend adding in at least a single pair of parentheses like this, for readability:

[COLOR=#333333]IIf([/COLOR][COLOR=#ff0000]([/COLOR][COLOR=#333333][ADMISSION SVS CD]="EOP" Or [ADMISSION SVS CD]="EO2"[/COLOR][COLOR=#ff0000])[/COLOR][COLOR=#333333] And [I/O]="O","Y","N")[/COLOR]

That just makes it more obvious to the reader to process the "Or" part first, and then the "And", instead of vice versa.

 

[DRANDON]

Actually this worked perfectly,

IIf([ADMISSION SVS CD]="EOP" And [I/O]="O" Or [ADMISSION SVS CD]="EO2") And [I/O]="O","Y","N")

 

[Joe4]

[COLOR=#333333]IIf([ADMISSION SVS CD]="EOP" And [I/O]="O" Or [ADMISSION SVS CD]="EO2") And [I/O]="O","Y","N")[/COLOR]

That cannot work as written, it has unbalanced parentheses (only one left one and two right ones).

And why are you checking [I/O] twice for the same value? The second one after the AND is totally unnecessary.

 

[DRANDON]

Though not necessary, I would recommend adding in at least a single pair of parentheses like this, for readability:

[COLOR=#333333]IIf([/COLOR][COLOR=#ff0000]([/COLOR][COLOR=#333333][ADMISSION SVS CD]="EOP" Or [ADMISSION SVS CD]="EO2"[/COLOR][COLOR=#ff0000])[/COLOR][COLOR=#333333] And [I/O]="O","Y","N")[/COLOR]

That just makes it more obvious to the reader to process the "Or" part first, and then the "And", instead of vice versa.

Yes, I see what you mean about readability, thank you. Turns out, I needed to put the And [I/O]="O" into the first section for accuracy. So it became:

IIf([ADMISSION SVS CD]="EOP" And [I/O]="O" Or [ADMISSION SVS CD]="EO2") And [I/O]="O","Y","N")

 

[xenou]

Yes, I see what you mean about readability, thank you. Turns out, I needed to put the And [I/O]="O" into the first section for accuracy. So it became:

IIf([ADMISSION SVS CD]="EOP" And [I/O]="O" Or [ADMISSION SVS CD]="EO2") And [I/O]="O","Y","N")

I guess I'll just repeat what Joe said - the parentheses are unbalanced as posted (syntax problem), and the I/O = "O" check two times is redundant (doesn't improve accuracy).

It's actually hard to know what your intention is with the broken parentheses. Do both or only one of the [ADMISSION SVS CD] values need to go with the [I/O] = "O" check?