Breaking Down IIF()
If you are getting lost in a giant IIF() statement, and you want to stick to keeping this stuff in the query, here’s how I do it sometimes, since I’m not the best with VBA.
Simply break your IIF’s down into separate fields, then use these fields in your last IIF() statement.
In the query it looks like you have a few different groups of cables. Or at least you can use the similarities in the string values to break it down. You can group them however you want to, here’s an example:
1Cx: IIF(Left([cable type], 3) = “1Cx”, IIF(Mid([cable type], 4, 1) = “5”, “500”, “4/0”))
3Cx: IIF(Left([cable type],1) = “3”, IIF(Mid([cable type], 4, 1) = “1”, “1/0”, IIF(Mid([cable type], 4, 1) = “3”, “350”, “500”)))
2Cx: IIF(Left([cable type], 1) = “2”, “16” IIF(Mid([cable type], 2,1) = “2”, “14”, “No Match”))
Now that I have grouped most of the cable type’s, I only have one remaining cable type (“Cable By Others”) from the ones you listed. My last field will be written like this:
CableValues: IIF(Left([cable type], 2) = “Ca”, “10”, IIF(Left[cable type], 1) = “3”, [3Cx], IIF(Left([cable type], 2) = “1C”, [1Cx], [2Cx])))
Make sure this [CableValues] field comes after the other 3 grouped fields in the query (i.e. to the right of them in the GUI).
This should simplify the IIF() statement to a more manageable status. That Code Mile-O-Phile gave is probably better… But this should work if you have issues with VBA.
Also Ken’s idea of a table would probably work very well. You can make a table and link it to your query with all the return values in another field…
Anyway…hope this can help you. GL!
Gary