Access SQL IIF Complex

Question

I am trying to change what one column data states to another using iif statements (ie: iif([major]="eet","electronic engineering technology")).

I got it to work until I added a new major to the group called PLS I get an box that states the expression is too complex.

When I take that one out it works fine. Is there a limit to the amount of iif statement for access?

Majors: IIf([Major]="EET","Electronics Engineering Technology",
IIf([Major]="DMA","Digital Media Arts Technology",
IIf([Major]="BAM","Business Administration - Management ",
IIf([Major]="Ess","Industrial Electronics Technology - Electronic Security Systems",
IIf([Major]="FMT","Facilities Management Technology",
IIf([Major]="FMTC","Facilities Management Technology Certificate",
IIf([Major]="HIT","Health Information Technology",
IIf([Major]="HSE","Human Services",
IIf([Major]="HVAC","Heating, Ventilation, Air Conditioning and Refrigeration Technology",
IIf([Major]="IENET","Industrial Electronics Technology - Computer and Networking Track",
IIf([Major]="Auto","Automotive Technology",
IIf([Major]="AT","Accounting Technology",
IIf([Major]="IETC","Industrial Electronics Technology-  Computer Track",
IIf([Major]="IETR","Industrial Electronics Technology - Railway Electronics Systems",
IIf([Major]="PLS","test","Ophthalmic Dispensing"))))))))))))))

Answer 1

One option would be to use a Switch() statement instead of the IIf():

Majors: Switch([Major]="EET","Electronics Engineering Technology",
               [Major]="DMA","Digital Media Arts Technology",
               ...,
               [Major]="PLS","test",
               True, "Ophthalmic Dispensing")

While the VBA Switch Function appears to accept a large number of parameters (I went 22 levels deep before I stopped testing), the Jet/ACE db engine's implementation of Switch appears to be capped at a maximum of 14 levels. As you correctly pointed out, it chokes on 15 or more.

That appears to leave the lookup table (described below) as the only practical option. (There are other less practical options, like writing your own Switch() function that takes an arbitrarily large number of parameters, but I think that is silly when the lookup table is such an obvious choice here.)

---

A better option would be to create a lookup table and JOIN it to your query. Use an INNER JOIN if you can be sure that there will be a row in the lookup table for every "Major" abbreviation. Use an OUTER JOIN with an Nz() or IIf() if you can't be sure.

Answer 2

If you have a back-end located on MS SQL Server and connect your tables via ODBC you could use the CASE WHEN THEN END CASE statement in SQL code, but in MS Access you can't.

The limitation you are likely to bump into is the stack depth limitation for SQL queries: every nested expression requires query analyzer to go one step deeper and at certain time point your stack is depleted and then it fails. Besides, there is a limitation to the total number of symbols in a SQL query, but this is large enough and should be OK here.

A possible a quick overcome for your solution (though it makes your query effectively slower) is to put the values to the separate table and perform look-up using DLookUp(Table, Field, Criteria) function in MS Access. Alternatively you can just join the table over your abbreviations and then convert your query into a full-fledged table (aka materialized view).

Answer 3

I would run a VBA loop with a query like this

UPDATE TableName SET Major = [LongName] WHERE Major = [ShortName]

where [LongName] and [ShortName] are parameters.

But really you're better off leaving the abbreviations in the column and creating a lookup table that you can join to when you want to use the long names. It's far more efficient in terms of data storage.

Answer 4

Create a two column lookup table, populate with your IIF mappings then join to this table. This will yield advantages e.g. referential integrity, when these values it is easier to change the table rather than change all the queries, etc.

Answer 5

What I am going to do is create separate queries for each major and then pass either iif or switch. In the end it helps the end user with exporting the data to powerpoint because they won't have to sort through the data.

Answer 6

I ended up here after hitting the limit for the switch function in MS Access. In case it helps anyone in my position, here is what I did.

I hit the limit for switch at 10. What worked for me was to use & to combine two switch statements.

Switch(CityName = "London", "English", 
              CityName = "Rome", "Italian", 
              CityName = "Paris", "French") code here
&
Switch(CityName = "New York", "English", 
              CityName = "Turin", "Italian", 
              CityName = "Bourgogne", "French")