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:
#1)
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:
#2
This errors variations of with or without parentheses, and returns wrong number of arguments:
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!
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:
#1)
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:
- IIf( ([SalesRep2] Is Not Null) And ([SalesRep3] Is Null) And ([InvSplit] Is Null),”O2”,
- IIf( ([SalesRep2] Is Not Null) And ([SalesRep3] Is Not Null) And ([InvSplit] Is Null),”O3”,
- IIf (([SalesRep2] Is Not Null) And ([SalesRep3] Is Null) And ([InvSplit] Is Not Null),”RS”,”N”) )))
#2
This errors variations of with or without parentheses, and returns wrong number of arguments:
- IIf(IsNotNull([SalesRep2]) And (IsNull([SalesRep3]) And (IsNull([InvSplit]),”O2”,
- IIf(IsNotNull([SalesRep2]) And (IsNotNull([SalesRep3]) And (IsNull([InvSplit]),”O3”,
- 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!