Mulitiple/Nested IIF statements

rohit_ghosh · May 1st, 2007, 08:45 AM

Hi,

Can anyone tell me the right way of writing multiple/Nested IIF statements. I am having a problem with Syntax. Also how many multiple IIF statements can i right in an expression. I am asking this because i have to change a field couple of times based on several conditions......with a single expression which has multiple IIF statements......also can somebody let me if this not possible in SQL is there a way to do this in VBA and add it to the query...to add to it i am not using a form to run this query....

stealthdevil · May 1st, 2007, 11:18 AM

You can use an Else If statement in VBA like the one below. If you just use a query you can build it into the criteria.

Code:

    If sLine = "11ALR" Then
        DoCmd.OpenQuery "qry_11ALR"
        DoCmd.OpenQuery "Q_11ALR2"
    ElseIf sLine = "11BLR" Then
        DoCmd.OpenQuery "qry_11BLR"
        DoCmd.OpenQuery "Q_11BLR2"
    ElseIf sLine = "11CLR" Then
        DoCmd.OpenQuery "qry_11CLR"
        DoCmd.OpenQuery "Q_11CLR2"
    ElseIf sLine = "11DLR" Then
        DoCmd.OpenQuery "qry_11DLR"
        DoCmd.OpenQuery "Q_11DLR2"
    ElseIf sLine = "11ELR" Then
        DoCmd.OpenQuery "qry_11ELR"
    ElseIf sLine = "12ALR" Then
        DoCmd.OpenQuery "qry_12ALR"
        DoCmd.OpenQuery "Q_12ALR2"
    ElseIf sLine = "12BLR" Then
        DoCmd.OpenQuery "qry_12BLR"
        DoCmd.OpenQuery "Q_12BLR2"
    ElseIf sLine = "12CLR" Then
        DoCmd.OpenQuery "qry_12CLR"
        DoCmd.OpenQuery "Q_12CLR2"
    ElseIf sLine = "14ALR" Then
        DoCmd.OpenQuery "qry_14ALR"
        DoCmd.OpenQuery "Q_14ALR2"
    ElseIf sLine = "14BLR" Then
        DoCmd.OpenQuery "qry_14BLR"
        DoCmd.OpenQuery "Q_14BLR2"
    ElseIf sLine = "14CLR" Then
        DoCmd.OpenQuery "qry_14CLR"
        DoCmd.OpenQuery "Q_14CLR2"
    End If

stealthdevil · May 1st, 2007, 11:22 AM

Here is a nested IF statement in VBA. It also contains a Do Loop.

Code:

        If iMax <> "" Then
            rsCur.MoveFirst
            Do Until rsCur.EOF
                sLimit = Mid(rsCur![CURRENT1], 5, 4)
                iLimit = CInt(sLimit)
                If sLine = "11ALR" Or sLine = "11CLR" Or sLine = "11DLR" Or sLine = "11ELR" Or sLine = "12ALR" Then
                    If iLimit > iMax Then
                        rsCur.Edit
                        rsCur![CURRENT1] = Null
                        rsCur.Update
                    End If
                Else
                    If iLimit < iMax Then
                        rsCur.Edit
                        rsCur![CURRENT1] = Null
                        rsCur.Update
                    End If
                End If
                rsCur.MoveNext
            Loop
        End If

pjm · June 1st, 2007, 10:44 AM

I believe that the limit to any expression in a query is 255 characters. Nesting in IIF is fairly straight-forward (although it can make your eyes go bleary).

IIF( condition1, IIF( condition2, IIF( condition2b, answer, answer), IIF( condition2c, answer, answer) ), IIF( condition3, answer, answer) )

and so on ...

Just get those pesky parentheses and commas right!

-Phil-