iif and or not working in Access update query

DRANDON

Active Member
Joined
Jun 30, 2006
Messages
264
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

DRANDON

Active Member
Joined
Jun 30, 2006
Messages
264
I figured it out.

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
Try like this:
Code:
[COLOR=#333333]IIf((([ADMISSION SVS CD]="EOP" Or [ADMISSION SVS CD]="EO2") And [I/O]="O"),"Y","N")[/COLOR]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
[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

Active Member
Joined
Jun 30, 2006
Messages
264

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
Code:
[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

Active Member
Joined
Jun 30, 2006
Messages
264
Though not necessary, I would recommend adding in at least a single pair of parentheses like this, for readability:
Code:
[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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,692
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,635
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top