IIF or Case? Please help, there has to be a better way.

N

NBullock

Might there be a better way? I am trying to pull text from a field in one
table and then assign those records to that result. For example, if the
printer name contains "gen" it is a member of the "Medical" department or if
it contains "acct" then it is a member of the Accounting department - and so
on.

I do not know of any other way to make this comparison other than maybe a
case statement, but I don't know the syntax for that in SQL.

Can anyone help?

SELECT IIf([PrinterTest] Is Not Null,[PrinterTest],[PrinterTest2]) AS
PrinterLists, MeditechPrinters.PrinterName, IIf([PrinterName] Like
"*gen*","Medical",IIf([PrinterName] Like "*NS*","Medical",IIf([PrinterName]
Like "*ACCT*","Accounting",IIf([PrinterName] Like
"*ADMN*","Administration",IIf([PrinterName] Like
"*ADMT*","Admitting",IIf([PrinterName] Like "*ADOL*","Adolescent
MH",IIf([PrinterName] Like "*BO*","Bussiness Office",IIf([PrinterName] Like
"*BUS*","Community Relations",IIf([PrinterName] Like
"*CARD*","Cardio",IIf([PrinterName] Like "*RHAB*","Rehab",IIf([PrinterName]
Like "*QA*","Quality",IIf([PrinterName] Like
"*RAD*","Radiology",IIf([PrinterName] Like
"*PHRM*","Pharmacy",IIf([PrinterName] Like "*CASH*","Cashier"))))))))))))))
AS PrinterTest, IIf([PrinterName] Like "*CCA*","Cancer
Care",IIf([PrinterName] Like "*CENT*","Central Sterile",IIf([PrinterName]
Like "*CLAB*","Cath Lab",IIf([PrinterName] Like
"*OB*","Obstetrics",IIf([PrinterName] Like "*COMR*","Community
Relations",IIf([PrinterName] Like "*DIA*","Diagnostic",IIf([PrinterName]
Like "*PER*","Personnel",IIf([PrinterName] Like
"IHR*","Personnel",IIf([PrinterName] Like
"*ULTRA*","Radiology",IIf([PrinterName] Like
"*PREA*","Preadminission",IIf([PrinterName] Like
"*PREN*","Obstertrics"))))))))))) AS PrinterTest2

FROM MeditechPrinters;
 
Top