Query help

[unclefink]

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]

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]

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]

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]

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

 

[June7]

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]

Are you trying to confuse him?

 

[unclefink]

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.