Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
IIf and multiple criteria 1
- Thread starter hyperiest
- Start date
-
1
- #2
- Thread starter
- #3
Thanks a mil, but it didn't work. This is my code:
MOSQIASI: IIf(Left([PMOS],3)="91W",Left([PMOS],3) & IIf(Mid([PMOS],5,1<>"F",Null,Mid([PMOS],5,1) & IIf(Mid([PMOS],6,2)="00",Null,Mid([PMOS],6,2),IIf(LEFT([PMOS],3)="91X", Left([PMOS],3) & Mid([PMOS],5,1) & IIf(Mid([PMOS],6,2)="00",Null,Mid([PMOS],6,2),Left([PMOS],3))
I have tried adding and subtracting parens at the end and throughout the statement, but to no avail. I keep getting that it has the wrong number of arguments.
MOSQIASI: IIf(Left([PMOS],3)="91W",Left([PMOS],3) & IIf(Mid([PMOS],5,1<>"F",Null,Mid([PMOS],5,1) & IIf(Mid([PMOS],6,2)="00",Null,Mid([PMOS],6,2),IIf(LEFT([PMOS],3)="91X", Left([PMOS],3) & Mid([PMOS],5,1) & IIf(Mid([PMOS],6,2)="00",Null,Mid([PMOS],6,2),Left([PMOS],3))
I have tried adding and subtracting parens at the end and throughout the statement, but to no avail. I keep getting that it has the wrong number of arguments.
hyperiest-
Every IIf() test must have a TRUE and a FALSE parameter. Several of your IIfs lack a FALSE value. It is usually easiest to start with one IIf and verify that the partial result works, then add on the next IIf.
The other big problem you have is that you are trying to concatenate Nulls. Anything plus Null equals Null, so for those FALSE arguments where you have put Nulls you need to switch them to an empty set "".
I can't figure out what exactly you're trying to do, thus I'm afraid that's about as specific as I can get.
-Larry
Every IIf() test must have a TRUE and a FALSE parameter. Several of your IIfs lack a FALSE value. It is usually easiest to start with one IIf and verify that the partial result works, then add on the next IIf.
The other big problem you have is that you are trying to concatenate Nulls. Anything plus Null equals Null, so for those FALSE arguments where you have put Nulls you need to switch them to an empty set "".
I can't figure out what exactly you're trying to do, thus I'm afraid that's about as specific as I can get.
-Larry
- Thread starter
- #5
Larry, I am an Army personnel officer who is sorting through Army SQL data tables to find discrepancies between authorized personnel strength from one database to another (sadly the Army has not caught on to using one central automated personnel system). I have common data across the different tables, but the data format is not common.
I am trying to select data based on specific MOS (military occupational specialty), but some of the data is concatenated (up to 9 characters with standardized placeholders for null values -- "00", "F", "O", "YY", etc.) and some of it is separated with null values instead of the standardized placeholders.
A typical MOS is as follows: 98G4L2SRU, where 98G denotes the MOS, 4 represents skill level, L denotes Linguist, 2S denotes Battle Staff qualification, RU denotes Russian language.
I am looking for all MOS's, but specifically 91W and 91X where the 5th character is "F" (also need those without F as a whole) and where the 7th and 8th characters are other than "00" (the standard placeholders for null value)--(also need those without "00" by specific value).
So, I want the query to return results like so:
MOS Auth
88M 75
91W 221
91WF 34
91WF2S 12
91W2S 10
91X 112
If I need to be more specific, let me know.
I am trying to select data based on specific MOS (military occupational specialty), but some of the data is concatenated (up to 9 characters with standardized placeholders for null values -- "00", "F", "O", "YY", etc.) and some of it is separated with null values instead of the standardized placeholders.
A typical MOS is as follows: 98G4L2SRU, where 98G denotes the MOS, 4 represents skill level, L denotes Linguist, 2S denotes Battle Staff qualification, RU denotes Russian language.
I am looking for all MOS's, but specifically 91W and 91X where the 5th character is "F" (also need those without F as a whole) and where the 7th and 8th characters are other than "00" (the standard placeholders for null value)--(also need those without "00" by specific value).
So, I want the query to return results like so:
MOS Auth
88M 75
91W 221
91WF 34
91WF2S 12
91W2S 10
91X 112
If I need to be more specific, let me know.
hyperiest-
Why don't you create a query that makes new fields for all the items concatenated in that expression. Your first query would have the additional fields of:
MOS:Left([PMOS],3)
Auth:Mid([PMOS],3,2)
etc., to get the string broken down. Then make a second query based on the first one that picks the records you want (MOS="91W" or "91X", etc.).
This avoids using a large, complicated IIf formula, but is also breaks the fields down so that you can, should the need ever arise, also pull out MOS="90Y" or whatever else you might need.
-Larry
Why don't you create a query that makes new fields for all the items concatenated in that expression. Your first query would have the additional fields of:
MOS:Left([PMOS],3)
Auth:Mid([PMOS],3,2)
etc., to get the string broken down. Then make a second query based on the first one that picks the records you want (MOS="91W" or "91X", etc.).
This avoids using a large, complicated IIf formula, but is also breaks the fields down so that you can, should the need ever arise, also pull out MOS="90Y" or whatever else you might need.
-Larry
MichaelRed
Programmer
A slightly different process would be to generte a small procedure which is just the same logic as the (fixed) IIF statement. It is just a LOT easier to read and understand the code in a procedure than the query grid or 'ZOOM' window.
It also appears, to me, that it would be easier to set up seperate queries for the criteria, and combine the results via a union of the individual selections. Just glancing at the statementt provided, it is not even clear to me wheather the returned calculation should be a MOS, of just a flag denoting the MOS is of interest.
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
It also appears, to me, that it would be easier to set up seperate queries for the criteria, and combine the results via a union of the individual selections. Just glancing at the statementt provided, it is not even clear to me wheather the returned calculation should be a MOS, of just a flag denoting the MOS is of interest.
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
- Thread starter
- #8
- Status
Similar threads
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question