470,613 Members | 2,348 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,613 developers. It's quick & easy.

Trouble utilizing nested IIf Statement

Hello - and thank you for reading my question!
I have experience with IIf and Nested IIf, but I am completely stuck on this one. I have written in 15 versions of syntax and cannot get it to work.
Here's what I need - I have an query that I need to analyze four fields of each record. One of these is fields always contains a value, the others vary. They are all number fields, but the numbers are not for expressions - they are just ID's numbers within the company. There are two record sources bringing these into the query.
I am trying to create a field with my IIf statement that will categorize the record based on the pattern of data in these four fields.
I want a text code filled in as an identifier.
Here are examples of how the IIf will read, and what will be returned in IIFCODE:
PrimaryRep SalesRep2 SalesRep3 InvSplit IIFCODE:
774 *null* *null* *null* N
184 1125 1126 *null* O3
774 887 *null* *null* O2
774 239 *null 50 RS

Here are my closest examples, where sql is actually trying to run it, but I can't get either to work:
When this is entered, the Error is Too many closing parentheses, and when I take the last parentheses out, access changes my trueparts (i.e. "O2") to bracketed ["O2"], so it's putting up parameter boxes that I ok through, and returns a blank field:
  1. IIf( ([SalesRep2] Is Not Null) And ([SalesRep3] Is Null) And ([InvSplit] Is Null),O2,
  2. IIf( ([SalesRep2] Is Not Null) And ([SalesRep3] Is Not Null) And ([InvSplit] Is Null),O3,
  3. IIf (([SalesRep2] Is Not Null) And ([SalesRep3] Is Null) And ([InvSplit] Is Not Null),RS,N))))

This errors variations of with or without parentheses, and returns wrong number of arguments:
  1. IIf(IsNotNull([SalesRep2]) And (IsNull([SalesRep3]) And (IsNull([InvSplit]),O2,
  2. IIf(IsNotNull([SalesRep2]) And (IsNotNull([SalesRep3]) And (IsNull([InvSplit]),O3,
  3. IIf((IsNotNull([SalesRep2]) And ([IsNull[SalesRep3]) And (IsNotNull([InvSplit]),RS,N)))

The falsepart is if all of these are null, across each record - IIFCODE is N
Can anyone offer the correct syntax I would use to achieve this?

Thanks in advance!
Mar 5 '21 #1
0 1317

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Fernando Rodriguez | last post: by
reply views Thread by lowellturner | last post: by
3 posts views Thread by Michele Simionato | last post: by
10 posts views Thread by Philip Ronan | last post: by
3 posts views Thread by mrquan83 | last post: by
1 post views Thread by ehpratah | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.