NESTED IIF Editor ?

[GUIDO22]

Hi All

Does anyone know of - or has found an editor that makes for relatively easy construction of 'Nested IIf' statements?
I am having to write some horrendously long ones for my reports and it is giving me a headache - namely getting the number of brackets and their positions within the statement correct.

Thanks
Guido

 

[georgedwilkinson]

It is a difficult exercise. When I have to do things like this, I open the VBE, create a fake subroutine, and type in the syntax there. At least you get a little help from Intellisense that way. And I just tried and it works with Iif().

 

[dkinley]

For the ad nasuem ones I predefine a substitution scheme ahead of time and just use the letters A, B, C .... and then make appropriate substitutions once all the syntax is correct.

Indenting as a way to visual see what is going on helps, too.

IFF(A = x, B, _
     IIF (B = x, C, _ 'false A
          IIF( ... )))  'false B

-dK

 

[GUIDO22]

Thanks Chaps - this is OK on the diddy ones - but check out this baby, and it's still growing.....!!!!

IIf([StockIdentifier] = 1 Or [StockIdentifier] = 2, IIf([StockIdentifier] = 1, IIf(IsNull([SupplierId]), [Unit_Dia], Reporting_EOY_SrcMaterialUnitCost!Price), IIf(IsNull([SupplierId]), [Unit_Con], (([Weld_ID] + [Weld_OD]) * Forms![End Of Year Reports]!txtBlws) + (2 * Reporting_EOY_SrcMaterialUnitCost!Price))), IIf([StockIdentifier] = 5 Or [StockIdentifier] = 10, ((((((([CentreDia] / 2) ^ 2) * 3.141) * [CentreThk]) * 8) / 1000000) * (DLookup("Rate", "PRODUCTION_RATES", "Id=4"))), (IIf([StockIdentifier] = 9, DLookup("Rate", "PRODUCTION_RATES", "Id=4"), [UnitPrice]))))


Trust me - it's a REAL MINDF*CK

 

[dkinley]

Hehe .. I bet it is ....

I've attached a diagram that might help if you or others watching the post have not used anything like this. I've used Excel before to visually see the logic and ensure I have all the branches taken care of especially since the one you showing is divergent.

I am sure you already use something like this to keep em straight but for anyone else that might start getting into hairy areas like this.

I've adopted my own rules since I was taught this method so it works better for me but basically fish-boning and then collecting up all the pieces when done. Note, the actual argument goes in the cell below the numbering scheme so you can copy and paste when you collect them up.


-dK

 

[GUIDO22]

Thanks for the diagram - will try representing my 'mess'(!) as such - to try and make it a little simpler to make further additions.

I may write an editor application that enables construction of these pesky things with a little more ease. Im sure others would find this useful ... will stick a copy on this posting (if and), when I do.

Thanks
G

 

[dkinley]

Actually ... I could see you posting it on some site and probably fetching $20 a pop for it.

Man I wish I had some free time.

-dK

 

[boblarson]

Personally, I would suggest if it gets beyond 2 or 3 levels then you should write a function and just pass arguments to it.

 

[boblarson]

Personally, I would suggest if it gets beyond 2 or 3 levels then you should write a function and just pass arguments to it.

And the reason I would do that is

1. It is much easier to read.

2. It is more maintainable

3. It is faster as it doesn't have to evaluate ALL true and false (which IIF's will do).

 

[GUIDO22]

Bob

Whilst I agree with the theory here - in practice it is a little more complicated than that. In addition, IIF statements seem more forgiving when they encounter NULL Values in the parameter list. (as it is I have had to Nz prefix each param. passed to the function).

The primary problem I am having with this arrangement is this : I have an instance where - if I pass a single parameter to the function call - the correct result is returned ie.

GetCurrentStockValue (Nz[UnitPrice],0)

However, if I attempt to call the function with the whole list of parameters (I need to account for all types/instances of stock items) :

GetCurrentStockValue([StockIdentifier],Nz([SupplierId],0),Nz([Unit_Dia],0),Nz([UnitPrice],0),Nz([Unit_Con],0),Nz([Weld_ID],0),Nz([Weld_OD],0),Nz([CentreDia],0),Nz([CentreThk],0),Nz([ConsPerPk],0))

The very same record returns with 'Error' in the calculated column.

To try and identify the reason for this I have even re-defined the function body to simply return the UnitPrice value :

GetCurrentStockValue = sngPrice

But the same error value results.....?

Do you have any ideas why this could be happening?

 

[gemma-the-husky]

i agree with Bob, fwiw

yes, you may have to surround the parameters with nz(myparam,0)

(or you could use variant types in the function declaration to get around this)

either of these are small prices to pay for the much easier development environment

 

[boblarson]

Do you have any ideas why this could be happening?

What is your function? It would have to do with how you have built your actual function.

 

[GUIDO22]

Bob

I have attached the function that I have attempted to call from the SQL Query (in lieu of the NESTED IIF discussed earlier).

The error mentioned occurs for items reaching the final Else - here, it should simply return the Price value (sngPrice). For a handful of items only - #Error# results in the recordset. Yet, the actual items do have a valid value for price identified in the source tables.

As I mentioned, if I strip out all code from the routine GetCurrentStockValue() and simply return sngPrice and also reduce the param list to a single parameter ie. UnitPrice only - the erroneous items from earlier now show the correct values! Wierd huh?

 

[mvester]

I hate the editor in Access VB. Intelisense gets in the way (reminds me of Mr. Paperclip). So, when I am writing a complicated "anything", I use Notepad++ and paste the resulting code back into the VB editor. Notepad++ has some nice features like colour-coded highlighting and regular expression searching. Notepad++ will make it lot easier to code a very deeply nested IIF. Or perhaps that deeply nested IIF should be broken up into functions.

 

[GUIDO22]

I hate the editor in Access VB. Intelisense gets in the way (reminds me of Mr. Paperclip). So, when I am writing a complicated "anything", I use Notepad++ and paste the resulting code back into the VB editor. Notepad++ has some nice features like colour-coded highlighting and regular expression searching. Notepad++ will make it lot easier to code a very deeply nested IIF. Or perhaps that deeply nested IIF should be broken up into functions.

Notepad++ - have had a quick play around with it - excellent piece of software and you are right the colour coded highlighting is ideal for this type of checking, I'll shelve my plans for writing an editor now! THANKS