Multi-Valued Fields Within IIF Function - Access

Question

Hi,

I currently am running into errors when trying to create a query within my Access 2010 database for tracking procurement requests. The whole point of the query is to show which requests are pending her financial review. I am trying to create a new field using the IIF function to display if the pending requests have technically approved (different from her financial approval). What I want the new column to display is "Yes" if it has been technically approved, "No" if it is pending technical approval and "Unnecessary" if the request does need technical approval (only her approval). Here is what I have been inputting and working around:

Technical Approval: IIf([Technical Reviewer]="Review Unnecessary" Or "Pre-Approved","Unnecessary",IIf([Date Technical Approved] Is Not Null,"Yes","No"))

The error message I am receiving is that "the multi-valued field '[Technical Reviewer]' is not valid in the expression. With that being said, when inputting new records in the database for the [Technical Reviewer], there are (8) options which are selected via combo box depending on the request. When either 'Pre-Approved' or 'Review Unnecessary' is selected, that means the request is then only pending financial approval. I have been researching for the last couple hours about the issues/problems with multi-valued fields and possible ways to work around them via a query, but nothing has worked.

The version above is the one I believe is the closest to how it should be, but I am really looking for some assistance in figuring out a way to display "Unnecessary" when the query is ran to show my manager that it does not need technical approval.

P.S. the database I am working on is extremely large and performing any major changes to the data types at this point would jeopardize the data for me and my colleagues. 

Any help or tips would be greatly appreciated!

Answer 1

Hy,

try with:

Technical Approval: IIf([Technical Reviewer]="Review Unnecessary" Or [Technical Reviewer] = "Pre-Approved","Unnecessary",IIf([Date Technical Approved] Is Not Null,"Yes","No"))

Mimmo

Answer 2

Mimmo,

Thank you for the quick response...it really is appreciated! So using your same logic, I was able to go ahead and create a slightly edited version of the syntax you sent and it was able to work. This is what I used:

Technical Approval: IIf([Technical Reviewer].[Value]="Review Unnecessary" Or [Technical Reviewer].[Value]="Pre-Approved","Unnecessary",IIf([Date Technical Approved] Is Not Null,"Yes","No"))

Once I included the .[value] after [Technical Reviewer] it worked perfectly! Thank you so much for your help.