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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help w/IIF 1

Status
Not open for further replies.

Kayo

Programmer
Aug 13, 2001
28
US
How do I write the following query in JET SQL?

If PMOS In ("11b","11c","11d","11f") then PMOS = "11"
If PMOS In ("12b","12c","12d","12f") then PMOS = "12"
If PMOS In ("13b","13c","13d","13f") then PMOS = "13"
If PMOS In ("45b","45c","45d","45f") then PMOS = "45"
IF PMOS Not In ("11b","11c","11d","11f", "12b", "12c",
"12d","12f", "13b","13c","13d","13f","45b","45c","45","45f")then PMOS = "OTHER";
 
IIf(Instr("11b,11c,11d,11f,12b,12C,12d,12f,13b,13c,13d,13f,45b,45c,4545f",PMOS)>0, Left(PMOS,2),"Other"
 
Go14, thanks very much for replying. However, I don't understand your example; how it relates/solves my problem. Could you please explain/breakdown your example relative to each line of code in my example?

V/r,
 
It uses the instr function to determine if PMOS exists anywhere within the string which consists of all the strings you were looking for.
If Instr locates it in the string it returns the position it located it in which would be a number > 0 so we now use the iif function

iif instr(string) > 0 ,true , false)

since you are really just trying to get back the first 2 digits of pmos I use the left function in the true part

left(pmos,2) returns 11 or 12 or 13 or 45

if there is no match instr returns 0 so the false cause =
"Other"

so this either returns the first 2 digits of cmos if it is in the string or the word other

Now unless I totally misunderstood what you were attempting this should produce exactally what you wanted
 
Guess I should show how to use in a query

SELECT IIf(Instr("11b,11c,11d,11f,12b,12C,12d,12f,13b,13c,13d,13f,45b,45c,45d45f",PMOS)>0, Left(PMOS,2),"Other")as expr1
FROM yourtable

in the QBE grid just

place =IIF(Instr("11b,11c,11d,11f,12b,12C,12d,12f,13b,13c,13d,13f,45b,45c,45d45f",PMOS)>0, Left(PMOS,2),"Other")

on the grid
 
1. Gol4 thanks for your input. It led me to the following:

TRANSFORM Count([DB2_301].[EP_SSN]) AS CountOfEP_SSN
SELECT Left([DB2_301].[PMOS_CD],2) AS PMOS
FROM DB2_301
WHERE ((([DB2_301].[PARTITION_ID])=42) And (([DB2_301].[RECSTA_CD])="G") And (([DB2_301].[PAY_GRADE_ID]) In (1,2,3,4,5,6,7,8,9)))
GROUP BY Left([DB2_301].[PMOS_CD],2)
ORDER BY [DB2_301].[PAY_GRADE_ID] DESC
PIVOT [DB2_301].[PAY_GRADE_ID];

2. The code produces what I need. Go14, again thanks for everything.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top