Transform a complex SQL iif statement into a VBA function

Question

In a query I have an SQL iif statement that incorporates too many iif, therefore I cannot add any more iif, which is a problem.

To solve the problem, I had the idea to code a VBA function, but I am facing difficulties. Here is what I have, with a simple example where we have a Number in a field. In case the number is <0, the function Retrive() should retrieve the value of field TheDate, in case >0 the function should retrieve the value of the field TheOtherDate:

Public Function Retrive(NumberToCheck As Integer) As Date
Dim db As Database
Dim r As Recordset
Dim rsCount As Integer
Dim TheDate As Field, TheOtherDate As Field
Dim i As Integer

Set db = CurrentDb()
Set r = db.OpenRecordset("Table")
Set TheDate = r.Fields("TheDate")
Set TheOtherDate = r.Fields("TheOtherDate")

rsCount = r.RecordCount

r.MoveFirst

For i = 1 To rsCount
    If NumberToCheck < 0 Then
        Retrive = TheDate.Value
    End If
    If NumberToCheck > 0 Then
        Retrive = TheOtherDate.Value
    End If
    r.MoveNext
Next i

End Function

But this does not work because it retrieves the last record for each line, not the right lines.

You can use switch command instead IIF which is much easier to understand.. VBA functions in a sql qury might reduce your performance. — Krish

PeterT PeterT · Accepted Answer · 2015-04-19T00:35:59

Your For loop just keeps running until you reach the last record and then exits. You have to jump out of the loop when you reach the correct record (you decide how to determine this).

Option Explicit

Public Function Retrive(NumberToCheck As Integer) As Date
    Dim db As Database
    Dim r As Recordset
    Dim rsCount As Integer
    Dim TheDate As Field, TheOtherDate As Field
    Dim TheRightDate As Date
    Dim i As Integer

    Set db = CurrentDb()
    Set r = db.OpenRecordset("Table")
    Set TheDate = r.Fields("TheDate")
    Set TheOtherDate = r.Fields("TheOtherDate")

    rsCount = r.RecordCount

    r.MoveFirst

    TheRightDate = DateValue("1/15/2015")

    For i = 1 To rsCount
        If NumberToCheck < 0 Then
            Retrive = TheDate.Value
            '--- check here to see if you have the correct value
            '    and if so, the exit the loop
            If Retrive = TheRightDate Then
                Exit For
            End If
        End If
        If NumberToCheck > 0 Then
            Retrive = TheOtherDate.Value
            '--- check here to see if you have the correct value
            '    and if so, the exit the loop
            If Retrive = TheRightDate Then
                Exit For
            End If
        End If
        r.MoveNext
    Next i
End Function

Transform a complex SQL iif statement into a VBA function

1 Answers