Nested IIF vs Select Case 2

[jdttek]

Part of my app tracks the progress on resolving warranty claims.

I have a query field that uses a nested IIF stmt to indicate what the next progressive step in the process is by analyzing which "completion boxes" have been checked (Steps in the process are logged by checking a check box when each step is completed).

Simple example -
Claim Status:IIF(CheckIssued]=-1,"Case Closed",IIF([DenyLtrIssued]=-1 and [MerchVal]>[DenyAmt] ,"Issue Check for for Partial Deny",IIF ([DenyLtrIssued]=-1 and [MerchVal]=[DenyAmt],"Issue Full Deny Letter",IIF(DenyLtrIssued]=0 and [CreditAmt]>0 and [IssueCRedit]=-1, "Issue Check for Credit Amt" ......))))
It goes on and on.....

The IIF's are nested in reverse order so that last action is analyzed 1st.
The formula gets increasingly complex with each possible variation especially since there are so many possible combinations (deny whole claim, deny partial claim, accept whole claim, send letter 1 if deny all, send letter 2 if partial deny, send check if accepted, etc.
Though it seems to work OK, I also get surpises every now and then when I find I have missed a combination of events

THere must be an easier way!!!
I have tried convering this to a Select Case but cannot figure out how create the criteria in the Select Case line ( equivilent to one of the nested IIF's) Any help in pointing me in the right direction would be appreciated.

jdttek

 

[KarMac21]

I don't think Select Case is the way you want to go. With Select Case you can only look at one variable per Select Statement. I would recommend creating a Table with 2 fields. One field would contain Expression you want to evaluate and the Second field would contain the resulting Status if the Expression is True.

Then you could create a recordset and cycle through it until you find an Expression is True and select the Status from the Status field.

 

[jdttek]

Thanks. You confirmed what I was starting to feel about using Select Case.

When you say "expression in a Table", I was not aware you could create an expression in a table, only in queries. Could ypu please point me in the right direction as to how I would do the expression field in the table?

 

[KarMac21]

Lets say you have a field called "strExpression" in your table. You would then enter values such as "[CheckIssued]=-1" in the strExpression field and "Case Closed" in the strStatus field.

That way when you are cycling through your recordset set you could do this:

If rst!strExpression Then <do something with rst!strStatus>

Hope this helps,
Shane

 

[Frink]

Hallo,

You could write a function strGetStatus which takes it's dependent values as parameters, then use if, select or whatever to get the values you want.

I think I'm right in saying that the computer will always evaluate both parts of an IIf statement. This is inefficient if you have complex expressions, so an alternative is preferable.

- Frink

 

[MichaelRed]

A common technique to this type of evaluation is to use a value derived from the various flags (check boxes in your case). W/o the whole thing, consider the following:

Public Function basWrtyStat(ParamArray MyChks() As Variant) As Long

    Dim Idx As Integer
    Dim MyVal As Long

    While Idx <= UBound(MyChks)
        If (MyChks(Idx) <> 0) Then
            MyVal = MyVal + 2 ^ (Idx)
        End If
        Idx = Idx + 1
    Wend

    basWrtyStat = MyVal

End Function
Public Function basTstWrtyStat()

    Dim ChkBx(5) As Integer
    Dim Idx As Integer
    Dim MyChk As Long

    While Idx <= UBound(ChkBx)
        If (Rnd(Idx) >= 0.5) Then
            ChkBx(Idx) = -1
        End If

        Idx = Idx + 1
    Wend

    MyChk = basWrtyStat(ChkBx(0), ChkBx(1), ChkBx(2), ChkBx(3), ChkBx(4), ChkBx(5))

    Debug.Print MyChk

End Function
[code]

Thus the first routine shows a 'calculation' based on the number of check boxes, while hte second simply illustrates a manner of calling the first.  The First will always return a value which CAN be usaed in the select case statement block.  Moreover, the actual value returned by [b]basWrtyStat[/b] is unique to each possible combination of check boxes, thus the select case statement block can be constructed in such a manner that there CANNOT be any cases (or comination of conditions) which are not considered.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[scriverb]

jdttek, looks like you have a lot of really good advise here but don't forget about the Switch statement. It is similar to the IIF but considerably easier to understand when there are good number of expressions to be evaluated.

ACCESS Help:
Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.
Syntax
Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

I find that it is much easier to keep straight the logic by using Switch as opposed to many nested IFF's. Also, the expressions have no direction relationship to each other. You can have the first expression that is evaluated look at something completely different from the second and third. The one thing that must remembered is that is starts with the first expression and keeps analyzing until it finds one that is true. Consequently, the order by which they are coded is very important.

Just giving you another idea to solve your problem.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[janeatwork]

Really excellent suggestions MichaelRed and scriverb.

Thanks

Jane
jmooreathome@tiscali.co.uk