Query help (1 Viewer)

unclefink

Registered User.
Local time
Today, 06:51
Joined
May 7, 2012
Messages
184
I know this is likely a simple issue that i'm overlooking but i'm not getting the results i'm expecting to see.

CoverageStatus: IIf([employee]="X" or "Y" and IIf([Notes] Is Null,"pending",""))

The intent is to use this calculated result to reference on a report for conditional formatting.

I'm sure i'm reading too deep into and i'm pretty sure i have the correct syntax. Someone help.

Also if it at all helps, I have employee id and employee concatenated field with the name so i can use the employee name or employee id for the employee portion of this syntax, in the above example, i'm using the employee name.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,555
not clear what you are trying to achieve

I can tell you that this bit

IIf([employee]="X" or "Y"....

needs to be either

IIf([employee]="X" or [employee]="Y"....

or

IIf([employee] IN ("X","Y")….

but this does not make sense

...and IIf([Notes] Is Null,"pending",""))

suggest write out in words what is required
 

June7

AWF VIP
Local time
Today, 05:51
Joined
Mar 9, 2014
Messages
5,425
Perhaps:

CoverageStatus: IIf([employee] IN("X","Y"), IIf([Notes] Is Null, "pending", ""), "this if not X or Y"))

Recommend use employee ID. If you use employee ID and it is number type, don't use quote marks.
 

unclefink

Registered User.
Local time
Today, 06:51
Joined
May 7, 2012
Messages
184
I really wasn't very thorough, sorry about that.

I'm just trying to create a syntax that compares two record fields with three possible resolutions.

If employee name is X or y and Notes, (a different column) is null then return "pending".

The pending result is a prompt for us to complete the notes related to the record.
 

plog

Banishment Pending
Local time
Today, 08:51
Joined
May 11, 2011
Messages
11,613
CoverageStatus: Iif(([employee name]='X' OR [employee name]='y') AND IsNull([Notes]), "pending")
 

June7

AWF VIP
Local time
Today, 05:51
Joined
Mar 9, 2014
Messages
5,425
Might need the false return value:

CoverageStatus: IIf(([employee name]='X' OR [employee name]='y') AND IsNull([Notes]), "pending", "")

or

CoverageStatus: IIf([employee name] IN ('X','y') AND IsNull([Notes]), "pending", "")

or per Allen Brown's recommendation to avoid VBA function call:

CoverageStatus: IIf([employee name] IN ('X','y') AND [Notes] Is Null, "pending", "")
 

plog

Banishment Pending
Local time
Today, 08:51
Joined
May 11, 2011
Messages
11,613
Are you trying to confuse him?
 

unclefink

Registered User.
Local time
Today, 06:51
Joined
May 7, 2012
Messages
184
Thank you all for your help. I did use the syntax Iif(([employee name]='X' OR [employee name]='y') AND IsNull([Notes]), "pending")

This gave me the results I was looking for.


I appreciate everyone’s assistance with this question.
 

Users who are viewing this thread

Top Bottom