Alternative to IIf statement in MS Access

Question

I've got an IIf statement that should do what I want, but its too complex so I need an alternative way of writing this code. I've tried a switch statement but have the same issue. The purpose of the code is difficult to explain, but I'll try to keep it simple, as I've found the context is important while searching for solutions.

Basically, I'm using a query to output a datasheet that can be used as a mock matrix. It needs to be able to be customized, i.e. 5x5, 7x7, 10x10, etc. and the values displayed need to change to reflect the dimensions. One of the tables the query pulls from is just number values that I use the sum of to determine the output of that field. There is also a field labeled "[Matrix Size]" that determines the dimensions. Eventually, this will also dictate what information gets displayed based on those dimensions, but I haven't gotten that far yet. The overly complex IIf statement(s) is as follows.

    IIf([Matrix Size]<=0," ",
        IIf([Impact]=0,"1",
            IIf([Impact]+[Likelihood]=2,"Low",
                IIf([Matrix Size]<2," ",
                   IIf([Impact]+[Likelihood]=3,"Low", 
                       IIf([Matrix Size]<3," ",
                          IIf([Impact]+[Likelihood]=4,"Low",
                             IIf([Matrix Size]<4," ",
                                IIf([Impact]+[Likelihood]=5,"Low",
                                   IIf([Matrix Size]<5," ",
                                       IIf([Impact]+[Likelihood]=6,"Low",
                                          IIf([Matrix Size]<6," ",
                                             IIf([Impact]+[Likelihood]=7,"Low",
                                                IIf([Matriz Size]<7," ",
                                                  IIf([Impact]+[Likelihood]=8,"Low","Error")))))))))))))))

If anyone knows of a way to simplify this/an alternate way of going about it that wont be too complex for access, please let me know. Any help is appreciated.

For Clarity, if needed:

The second IIf is used to set the first row to be "1", to act as one of the numerical values along a horizontal row. The impact+likelihood statements determine if the field should display a value of "Low","Medium", or "High", but for now "Low" is just a placeholder, and the matrix size statements determine if it should be blank or not based on desired dimensions.

It sounds like you need a lookup table because this code is bananas. A lookup table is just an array. — tadman
@tadman Would an array be able to be changed in the way I need it to? I haven't done much with arrays before so I'm not sure what it's capabilities are. — Roland
An array is just a list of things, and the nice thing about an array is it's a concept your code can work with using a handful of lines instead of you having to spell things out, step by step. A good place to start is documentation. You could make an array that maps 0 to "1", 1 to "2", 2 to "Low" and so on, which is easy because arrays are zero indexed. — tadman
If you can't do a lookup table due to complexity of rules, write a VBA function. — SunKnight0

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-05-17T18:24:18

The alternative you are looking for is switch():

switch([Matrix Size] <= 0, " ",
       [Impact] = 0, "1",
       [Impact] + [Likelihood] = 2, "Low",
       . . .
       "Error"
      )  -- only one paren needed!

Unfortunately, your conditions are a mixture based on the three columns, so a lookup table would be a little bit cumbersome.

Alternative to IIf statement in MS Access

1 Answers