IIF statement help...

[mrenshaw]

I am trying to change a + number to a – number in a query based on an items first character of “R”. Can’t seem to get it to work. Here is an example of what I am doing. IIf([Item]=”R”*,-[Total],[Total]).
I know this is wrong but I thought this would help illustrate what I am trying to do. Any help would be greatly appreciated!
Mike

 

[Ukraine82]

Try this

IIf([Item] Like "R*",-[Total],[Total])

hth,
Michael

 

[Mile-O]

IIf(Left$([Item], 1) = "R", -[Total], [Total])

 

[mrenshaw]

Ahhh.... it was the like "R"*! The IIf([Item] Like "R*",-[Total],[Total]) worked perfect! Thanks!!

 

[Mile-O]

mrrenshaw,

The Left$() method that I posted is faster than the Like method. You can see this for yourself if you do the following:

1. Open a new module;
2. Copy the code below into the module;
3. Put the cursor within the function;
4. Press F5 to run the function.

Private Declare Function WWindowsStart Lib "WINMM" Alias "timeGetTime" () As Long

Function Test1()
    
    Dim lngStartA As Long, lngStopA As Long, lngResultA As Long
    Dim lngStartB As Long, lngStopB As Long, lngResultB As Long
    Dim strMainText As String, lngCounter As Long
    Dim booPointless As Boolean
    
    strMainText = "Rabbit"
    
    lngStartA = WWindowsStart
    For lngCounter = 1 To 100000
        If strMainText Like "R*" Then booPointless = True
    Next lngCounter
    lngStopA = WWindowsStart
    
    lngStartB = WWindowsStart
    For lngCounter = 1 To 100000
        If Left$(strMainText, 1) = "R" Then booPointless = True
    Next lngCounter
    lngStopB = WWindowsStart
    
    lngResultA = lngStopA - lngStartA
    lngResultB = lngStopB - lngStartB
    
    If lngResultA = lngResultB Then
        MsgBox "They are the same.", vbInformation
    ElseIf lngResultA < lngResultB Then
        MsgBox "Like 'Text*' is faster than Left$(Text, 1) = Text'" & _
            vbCrLf & "A: " & lngResultA & " milliseconds" & _
            vbCrLf & "B: " & lngResultB & " milliseconds", vbInformation
    Else
        MsgBox "Left$(Text, 1) = Text' is faster than Like 'Text*'" & _
            vbCrLf & "A: " & lngResultA & " milliseconds" & _
            vbCrLf & "B: " & lngResultB & " milliseconds", vbInformation
    End If

End Function

While the result is only in milliseconds (and that's for 100000 iterations) you may not be concerned about the time that it takes to perform the comparison but as your record count grows the milliseconds can begin to pile up.

By using the code above you will get an idea of the times - roughly 45 milliseconds for my suggested method and 130 milliseconds for Ukraine's.

Now, you want to use an IIf() as part of your comparison. Create a new module, add the following code and test this function.

Private Declare Function WWindowsStart Lib "WINMM" Alias "timeGetTime" () As Long

Function Test2()
    
    Dim lngStartA As Long, lngStopA As Long, lngResultA As Long
    Dim lngStartB As Long, lngStopB As Long, lngResultB As Long
    Dim strMainText As String, lngCounter As Long
    Dim booPointless As Boolean
    
    strMainText = "Rabbit"
    
    lngStartA = WWindowsStart
    For lngCounter = 1 To 100000
        booPointless = IIf(strMainText Like "R*", True, False)
    Next lngCounter
    lngStopA = WWindowsStart
    
    lngStartB = WWindowsStart
    For lngCounter = 1 To 100000
        booPointless = IIf(Left$(strMainText, 1) = "R", True, False)
    Next lngCounter
    lngStopB = WWindowsStart
    
    lngResultA = lngStopA - lngStartA
    lngResultB = lngStopB - lngStartB
    
    If lngResultA = lngResultB Then
        MsgBox "They are the same.", vbInformation
    ElseIf lngResultA < lngResultB Then
        MsgBox "Like 'Text*' is faster than Left$(Text, 1) = Text'" & _
            vbCrLf & "A: " & lngResultA & " milliseconds" & _
            vbCrLf & "B: " & lngResultB & " milliseconds", vbInformation
    Else
        MsgBox "Left$(Text, 1) = Text' is faster than Like 'Text*'" & _
            vbCrLf & "A: " & lngResultA & " milliseconds" & _
            vbCrLf & "B: " & lngResultB & " milliseconds", vbInformation
    End If

End Function



Function Test1()
    
    Dim lngStartA As Long, lngStopA As Long, lngResultA As Long
    Dim lngStartB As Long, lngStopB As Long, lngResultB As Long
    Dim strMainText As String, lngCounter As Long
    Dim booPointless As Boolean
    
    strMainText = "Rabbit"
    
    lngStartA = WWindowsStart
    For lngCounter = 1 To 100000
        If strMainText Like "R*" Then booPointless = True
    Next lngCounter
    lngStopA = WWindowsStart
    
    lngStartB = WWindowsStart
    For lngCounter = 1 To 100000
        If Left$(strMainText, 1) = "R" Then booPointless = True
    Next lngCounter
    lngStopB = WWindowsStart
    
    lngResultA = lngStopA - lngStartA
    lngResultB = lngStopB - lngStartB
    
    If lngResultA = lngResultB Then
        MsgBox "They are the same.", vbInformation
    ElseIf lngResultA < lngResultB Then
        MsgBox "Like 'Text*' is faster than Left$(Text, 1) = Text'" & _
            vbCrLf & "A: " & lngResultA & " milliseconds" & _
            vbCrLf & "B: " & lngResultB & " milliseconds", vbInformation
    Else
        MsgBox "Left$(Text, 1) = Text' is faster than Like 'Text*'" & _
            vbCrLf & "A: " & lngResultA & " milliseconds" & _
            vbCrLf & "B: " & lngResultB & " milliseconds", vbInformation
    End If

End Function

This new function contains an IIf() contained with both methods of comparison. You will see that the inclusion of the IIf() means that my method now takes about 90 milliseconds for 100,000 iterations while the other takes about double that at 180 milliseconds.

If you are querying a table with this IIf() and the Like then your query will tale longer to run than IIf() and Left$ - especially with the more records you add to the base table.

 

[mrenshaw]

Wow! Very nice! Thanks for your time.