IIF LIKE to CASE Statement

[mg2rde]

To all
I inherited the following IIF query but it seems that there is a limitation as to how many access can handle. I would like to move the code to a Case select statement. Can someone help with the proper syntax? The code is the following:
if(a.[Hospital Currently Admitted to] Like '*STATEN*', 'Staten Island University Hospital',
if(a.[Hospital Currently Admitted to] Like '*BRONX*', 'Bronx-Lebanon Hospital Center',
if(a.[Hospital Currently Admitted to] Like '*ST*FRANC*', 'St Francis of Roslyn',
if(a.[Hospital Currently Admitted to] Like '*CORN*', 'NY Cornell',
if(a.[Hospital Currently Admitted to] Like '*HOSP*SPEC*S*', 'Hospital for Special Surgery',
if(a.[Hospital Currently Admitted to] Like '*HOSP*JT*D*', 'Hospital for Joint Diseases',
if(a.[Hospital Currently Admitted to] Like '*NYU*', 'NYU Medical Center',
if(a.[Hospital Currently Admitted to] Like '*SLOAN*', 'Memorial Sloan Kettering',
if(a.[Hospital Currently Admitted to] Like '*WESTCHESTER*', 'Westchester Square Med Center',
if(a.[Hospital Currently Admitted to] Like 'NY HOSP*Q*', 'New York Hospital Queens',
if(a.[Hospital Currently Admitted to] Like 'NY*PRESB*CP*', 'NYPH-Columbia',
if(a.[Hospital Currently Admitted to] Like 'NY*PRESB*A*', 'NYPH-Allan Pavilion',
if(a.[Hospital Currently Admitted to] Like 'MT*SINAI*H*', 'Mt Sinai Hospital',
if(a.[Hospital Currently Admitted to] Like '*METHODIST*', 'Methodist')))))))))))))) AS Hospital

Thanks in advance

 

[plog]

Proper syntax isn't the answer--proper data structure is.

You need to create a table called something like 'Hospitals' which has an ID number and all those hospitals that abortion of an IIF statement lists. Then you need to add a field to table [a] called something like hospitalid which will hold the corresponding number of a hospital from the Hospitals table. Next, you need to append the correct number for each [Hospital Currently Admitted to] field into the hospitalid field.

Now your data is properly normalized and when you want to display the hospital name you link [a] to [Hospitals] by that number and you can easily do it.

 

[mg2rde]

Thanks for your help.

 

[mg2rde]

thanks...it seems quite simply now