Flow chart from VBA IIF function

[MarcelMegens]

I frequently use complex iif statements in queries or in VBA and after a certain number of months when I look at these functions, I first have to manually draw a flowchart to see what the statement actually does.

Is there an automated way/tool which could do this for me.
(then I could also document the complex statements immideately - as I should be doing like all other programmers....) )

an example would be :

IIf([Contract status]="w","W",IIf([Quantity] Is Not Null And [Quantity]>0,IIf([escalation items].[PN] Is Not Null,"1",IIf([bo]>0,"2",IIf([sos] Is Not Null And [sos]+90>Date(),"3",IIf([ut]="4" Or [ut]="5","4",IIf([PartStatus]="y","5",IIf([sos] Is Not Null,"6","7")))))),IIf([sos] Is Not Null,"8","X")))

I did find a tool which analyses VBA into flow charts, but not IIF's or IF functions.

 

[namliam]

I find re-writing them like so:

IIf([Contract status]="w"
   ,"W"
   ,IIf(    [Quantity] Is Not Null   
        And [Quantity]>0
       ,IIf([escalation items].[PN] Is Not Null
           ,"1"
           ,IIf([bo]>0
               ,"2"
               ,IIf([sos] Is Not Null And [sos]+90>Date()
                   ,"3"
                   ,IIf([ut]="4" Or [ut]="5"
                       ,"4"
                       ,IIf([PartStatus]="y"
                           ,"5" 
                           ,IIf([sos] Is Not Null
                               ,"6"
                               ,"7"
                               )
                           )
                       )
                   )
               )
           )
       ,IIf([sos] Is Not Null
           ,"8"
           ,"X"
           )
       )
   )

Really helps for readability and maintainability...
On the other hand makins such things in a function and actually adding comments to it is probably a good idea

Though I understand your probably trying to keep your 1,2,3,4, etc in order, personaly I would keep shorter 'tracks' above... like so...

IIf([Contract status]="w"
   ,"W"
[b][u]   ,IIf(    nz([Quantity],0) = 0
       ,IIf([sos] Is Not Null
           ,"8"
           ,"X"
           )[/b][/u]
       ,IIf([escalation items].[PN] Is Not Null
           ,"1"
           ,IIf([bo]>0
               ,"2"
               ,IIf([sos] Is Not Null And [sos]+90>Date()
                   ,"3"
                   ,IIf([ut]="4" Or [ut]="5"
                       ,"4"
                       ,IIf([PartStatus]="y"
                           ,"5" 
                           ,IIf([sos] Is Not Null
                               ,"6"
                               ,"7"
                               )
                           )
                       )
                   )
               )
           )
       )
   )

 

[ions]

You can have a user defined function in Queries if your backend is not SQL server. IIF is probably more effecient in queries than user defined functions but when your IIF gets this complex I would use VBA nested IF statements.

 

[gemma-the-husky]

i wouldn't have a inline iif as complicated as that.

i would call a function with some arguments. much easier to write.

 

[MarcelMegens]

I was hoping for an easy way to simplify overcomplicated iif statements. I agree with you both that I sometimes would have to step back, decide that a function is more suitable and rewrite the iif... ;(

 

[Rx_]

A situation like this would probably warrnt a custom function using a Select Case statement if it is being used for a lot of records.
A Select Case when used in large Excel Object Model Programming to evaulate values is usually (it depends of course) much more efficient.
Years ago, we evaluated this as a group of 20 people in a three hour lab experiement. As stated above, this won't always apply to a back-end SQL Server. Note that evern SQL Server now offeres a Case statement in TSQL due to popular demand.

After I shared my old Excel 97 experience, thought it best to look this up for the modern day.
http://visualbasic.about.com/od/quicktips/qt/IfElseSelCase.htm
This post claims there is no difference in the outcome now. Maybe will drop this as a reason.

This post shows the basics of converting too many nested IIF to a Case Function.
http://www.simply-access.com/Too_Many_Nested_IIFs_Required.html
The reasons are centered around troubleshooting and code maintenance.