Too Many Nested IIFs Required

What happens if there are too many nested IIFs required for a nested IIF function, or you have so many nested IIF’s that it’s all getting just a bit too complicated?

Click the link if you wish to know more about Nested IIF Functions .

I thought there would be a maximum number of nested IIF statements, but I can’t seem to find any reference to it (the closest I could find was a reference to 7) but, if you need more than 2-3 nested IIF statements, things are likely to get way too complicated, it becomes very difficult to track where you are up to. As an example, try finding an error in this syntax (and this only includes 3 nested IIF statements):

Finish: IIf((IsNull([DeliveryCharge]) Or [DeliveryCharge]="") And [ExtraCartage]=0,"",IIf([ExtraCartage]=0,"" & [DeliveryCharge] & "",IIf(IsNull([DeliveryCharge]),"" & "EC" & "," & [ExtraCartage] & "","" & [DeliveryCharge] & "" & "EC" & "" & "," & [ExtraCartage] & "")))

So here is one option you can try if you are in the position of too many nested IIfs required.

The best option, once you get past 2-3 IIF functions, would be to call a customised function to update the fields for you. You will need to do some VBA coding, but I will take you through it step by step:

This following example is designed for a query and sends the value of the product field to the function. Depending on what the product is, a different value (brand of the product) is returned to the query.

To do this:

Click on Modules in the ‘Objects’ section of your database then, click on 'New'. This will open a blank screen with:

Option Compare Database

Option Explicit

Note: Option Explicit may or may not be there but, if it isn’t, type it in under Option Compare database.

Then, on the toolbar, click on New > Insert > Procedure.

In the window that opens:

Name: fSort

Type: function

Scope: Public

Click on OK when done. This will create the following two lines of code:

      Public Function fSort()

      End Function

In between the brackets next to fSort, type the following:

Product As String

So, your first line now looks like this:

      Public Function fSort(Product As String)

In between these two lines of code, type the following:

Change the product names to your product names and the appropriate branding. There is no limit to the Case statements, just add all the options there are.

The Case Else is just in case the product does not fall into any of the above. You will not get an error if you don’t use this, it just neatens things up.

The Select Case statement will end when it equates to true, it will not continue through the rest of the statements.

Once you have set this up, save it (call the module, mBranding) and close the VBA coding window by clicking on the most-outer X.

Now open your query in design view. In the first available column, type the following in the first row:

Brand:fSort([Product])

This will call the fSort function, passing the value of the field 'Product' to it and returning the branding value to this column.

Now you know what to do if you have too many nested IIFs.

Finished with Too Many Nested IIFs Required then return to Simply Access Home Page