IIF Statement Queries

[JMichaelM]

Hello and Good Morning,

I posted a few weeks ago about this and got some great feedback so wanted to share the situation further. I had a client of mine who approached me on a project that they are able to accomplish in MS Excel. They are looking to use MS Access for it as well however after hearing of it, it looks as though the queries with IIF expressions may be too complex to execute. Basically, they want to populate columns based on certain criteria. The scenario is a s follows:
=IF(DATA!BZ17="-1",DATA!CB17,IF(DATA!CE17="-1",DATA!CG17,IF(DATA!CJ17="-1",DATA!CL17,IF(DATA!CO17="-1",DATA!CQ17,IF(DATA!CT17="-1",DATA!CV17,IF(DATA!CY17="-1",DATA!DA17,IF(DATA!DD17="-1",DATA!DF17,IF(DATA!DI17="-1",DATA!DK17,IF(DATA!BX17="-1","Manager Review", IF(DATA!BY17="-1"," Check"))))))))))

 

[Ranman256]

If you need that many IIFs ,try alternates:
1.Use a lookup table join in the query.
Or
2.Create a custom function. The query would submit params (if needed) and the function would do the complex if work.
Or
3. Use a SWITCH statement.

 

[JMichaelM]

Any examples anywhere?

 

[Minty]

I think - having already answered in the previous thread, it would be better for you to give us some sample data - in the way you currently have it - and an example of the output you want from that data.
Example data in spreadsheets are fine at this point. Zip them if you can't post them unzipped.

 

[JMichaelM]

Here is a sample with no real data.