Calling a VBA Function in an Access Query
ms-access-2013, sql
Solution
I see several issues here:
re: "Undefined function" error - Make sure that your Function is in a standard VBA module.
Make sure that the Module includes an `Option Explicit` statement near the very top.
Ensure that your Function is in fact visible to your query. It is best to declare it explicitly as a `Public Function`.
Your Function does not actually return a value. It needs to assign `EHC = ExactHC` just before the final `End Function` statement.
Check your query to see if you really do need all of those INNER JOINs. I sincerely doubt that you do, since your `EHC()` function will pull the required information from those other queries in the process of calculating the Exact Handicap.
Problem
I am trying to combine the results of 8 different queries back into the one query. All the queries to be used are queries of queries of queries of queries. The 8 series of 4 queries split the players up depending on how many rounds of golf they have played. The last query in each series calculates the exact handicap of each player. What I am trying to do may not be possible with the code I am using. The function code is as follows: ``` Function EHC(PlayerID As Long) As Long Dim queT20 As Recordset Dim que3to6 As Recordset Dim que7or8 As Recordset Dim que9or10 As Recordset Dim que11or12 As Recordset Dim que13or14 As Recordset Dim que15or16 As Recordset Dim que17or18 As Recordset Dim que19or20 As Recordset Set queT20 = CurrentDb.OpenRecordset("Top20Count") queT20.FindFirst ("PlayerID =" & PlayerID) If queT20![PlayerID] >= 19 Then Set que19or20 = CurrentDb.OpenRecordset("P_19or20ExactHC") que19or20.FindFirst ("PlayerID =" & PlayerID) ExactHC = que19or20.Exact_HC que19or20.Close Set que19or20 = Nothing ElseIf queT20![PlayerID] >= 17 Then Set que17or18 = CurrentDb.OpenRecordset("P_17or18ExactHC") que17or18.FindFirst ("PlayerID =" & PlayerID) ExactHC = que17or18.Exact_HC que17or18.Close Set que17or18 = Nothing ElseIf queT20![PlayerID] >= 17 Then Set que15or16 = CurrentDb.OpenRecordset("P_15or16ExactHC") que15or16.FindFirst ("PlayerID =" & PlayerID) ExactHC = que15or16.Exact_HC que15or16.Close Set que15or16 = Nothing ElseIf queT20![PlayerID] >= 17 Then Set que13or14 = CurrentDb.OpenRecordset("P_13or14ExactHC") que13or14.FindFirst ("PlayerID =" & PlayerID) ExactHC = que13or14.Exact_HC que13or14.Close Set que13or14 = Nothing ElseIf queT20![PlayerID] >= 17 Then Set que11or12 = CurrentDb.OpenRecordset("P_11or12ExactHC") que11or12.FindFirst ("PlayerID =" & PlayerID) ExactHC = que11or12.Exact_HC que11or12.Close Set que11or12 = Nothing ElseIf queT20![PlayerID] >= 17 Then Set que9or10 = CurrentDb.OpenRecordset("P_9or10ExactHC") que9or10.FindFirst ("PlayerID =" & PlayerID) ExactHC = que9or10.Exact_HC que9or10.Close Set que9or10 = Nothing ElseIf queT20![PlayerID] >= 17 Then Set que7or8 = CurrentDb.OpenRecordset("P_7or8ExactHC") que7or8.FindFirst ("PlayerID =" & PlayerID) ExactHC = que7or8.Exact_HC que7or8.Close Set que7or8 = Nothing ElseIf queT20![PlayerID] >= 17 Then Set que3to6 = CurrentDb.OpenRecordset("P_3to6ExactHC") que3to6.FindFirst ("PlayerID =" & PlayerID) ExactHC = que3to6.Exact_HC que3to6.Close Set que3to6 = Nothing Else: ExactHC = 0 End If queT20.Close Set queT20 = Nothing End Function ``` and the SQL Query Statement: ``` SELECT PlayerInfo.PlayerID, PlayerInfo.Display, EHC([PlayerInfo]![PlayerID]) AS ExactHandicap FROM ((((((((PlayerInfo INNER JOIN Top20Count ON PlayerInfo.PlayerID = Top20Count.PlayerID) INNER JOIN P_3to6ExactHC ON PlayerInfo.PlayerID = P_3to6ExactHC.PlayerID) INNER JOIN P_7or8ExactHC ON PlayerInfo.PlayerID = P_7or8ExactHC.PlayerID) INNER JOIN P_9or10ExactHC ON PlayerInfo.PlayerID = P_9or10ExactHC.PlayerID) INNER JOIN P_11or12ExactHC ON PlayerInfo.PlayerID = P_11or12ExactHC.PlayerID) INNER JOIN P_13or14ExactHC ON PlayerInfo.PlayerID = P_13or14ExactHC.PlayerID) INNER JOIN P_15or16ExactHC ON PlayerInfo.PlayerID = P_15or16ExactHC.PlayerID) INNER JOIN P_17or18ExactHC ON PlayerInfo.PlayerID = P_17or18ExactHC.PlayerID) INNER JOIN P_19or20ExactHC ON PlayerInfo.PlayerID = P_19or20ExactHC.PlayerID WHERE (((PlayerInfo.Display)=True)); ``` When i try to run this i get an error message of `Undefined function 'EHC' in expression`. I have also tried the same query with either a IIF or SWITCH statement in replace of the function call. I am roughly basing the idea of the function call from the article in MS Access 2010 Ranking Query comparing two columns for unique ranks. Any help would be much appreciated as I only have this problem to sort out and I hope I will be close to finishing this self imposed nightmare.