-1

I wrote a function to use in an Access Query. The function work once but now it reverts back to the start of the routine when "Set rs = db.OpenRecordset etc." is hit.

Public Function RaceOutlook()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Fav1 As Integer
Dim Fav2 As Integer
Dim Outlook As Integer
Dim Pace As Integer
Dim nFav1 As Field
Dim nFav2 As Field
Dim nOutlook As Field
Dim nPace As Field
Set db = CurrentDb 'tells program to use the current db
Set rs = db.OpenRecordset("Race Card Contenders", dbOpenDynaset)


Fav1 = rs("nFav1")
Fav2 = rs("nFav2")
Outlook = rs("nOutlook")
Pace = rs("nPace")


Select Case True
    Case (Fav1 = 1 And Fav2 = 3 And Outlook = 1 And Pace = 5)
    RaceOutlook = 1
    Case Else
    RaceOutlook = 0
End Select
rs.Close
End Function
Smandoli
  • 6,919
  • 3
  • 49
  • 83
D. Gleason
  • 1
  • 1
  • 2
    You shouldn't use a function for this - just do a JOIN on `Race Card Contenders` in the query. That's what databases are for, that's how SQL works. And it will be much, much, MUCH faster! And easier. And, well, everything will be better. What is a JOIN? See here: https://stackoverflow.com/questions/17946221 – Leviathan May 10 '16 at 21:03
  • Agree. If you can get an understanding of SQL *OR* of functions, maybe you have a chance. – Smandoli May 10 '16 at 23:10

1 Answers1

1

In the MS Access UI (.exe only not ODBC/OLEDB) you can call a user-defined inline function directly from an SQL query even passing parameters with no need for opening the same recordset:

VBA function (to be placed in a module)

Public Function RaceOutlook(Fav1 As Integer, Fav2 As Integer, _
                            Outlook As Integer, Pace As Integer)

    Select Case True

       Case (Fav1 = 1 And Fav2 = 3 And Outlook = 1 And Pace = 5)
       RaceOutlook = 1

       Case Else
       RaceOutlook = 0

    End Select

End Function

SQL query

SELECT r.*, 
       RaceOutlook(r.fav1, r.fav2, r.Outlook, r.Pace) As RaceOutlook
FROM [Race Card Contenders] r

However, as commented, the VBA function does not offer more than Access's IIF() expression. Plus, below alternative query will work if connected to the database externally via ODBC/OLEDB:

SELECT r.*, 
       IIF(r.fav1=1 AND r.fav2=3 AND r.Outlook=1 AND r.Pace=5, 1, 0) As RaceOutlook
FROM [Race Card Contenders] r
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • The VBA solution works best since there are over 50 unique cases to be tested. Thank you for your valued response – D. Gleason May 11 '16 at 12:53
  • Great! Please accept answer to confirm resolution. Also, do note, `IIF()` can be nested to allow more conditions. – Parfait May 11 '16 at 13:26