title: Calling a VBA Function in an Access Query
body: <p>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.</p>
  
  <p>What I am trying to do may not be possible with the code I am using. </p>
  
  <p>The function code is as follows:</p>
  
  <pre class="lang-vbs prettyprint-override"><code>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 =" &amp; PlayerID)
      If queT20![PlayerID] &gt;= 19 Then
          Set que19or20 = CurrentDb.OpenRecordset("P_19or20ExactHC")
          que19or20.FindFirst ("PlayerID =" &amp; PlayerID)
          ExactHC = que19or20.Exact_HC
          que19or20.Close
          Set que19or20 = Nothing
      ElseIf queT20![PlayerID] &gt;= 17 Then
          Set que17or18 = CurrentDb.OpenRecordset("P_17or18ExactHC")
          que17or18.FindFirst ("PlayerID =" &amp; PlayerID)
          ExactHC = que17or18.Exact_HC
          que17or18.Close
          Set que17or18 = Nothing
      ElseIf queT20![PlayerID] &gt;= 17 Then
          Set que15or16 = CurrentDb.OpenRecordset("P_15or16ExactHC")
          que15or16.FindFirst ("PlayerID =" &amp; PlayerID)
          ExactHC = que15or16.Exact_HC
          que15or16.Close
          Set que15or16 = Nothing
      ElseIf queT20![PlayerID] &gt;= 17 Then
          Set que13or14 = CurrentDb.OpenRecordset("P_13or14ExactHC")
          que13or14.FindFirst ("PlayerID =" &amp; PlayerID)
          ExactHC = que13or14.Exact_HC
          que13or14.Close
          Set que13or14 = Nothing
      ElseIf queT20![PlayerID] &gt;= 17 Then
          Set que11or12 = CurrentDb.OpenRecordset("P_11or12ExactHC")
          que11or12.FindFirst ("PlayerID =" &amp; PlayerID)
          ExactHC = que11or12.Exact_HC
          que11or12.Close
          Set que11or12 = Nothing
      ElseIf queT20![PlayerID] &gt;= 17 Then
          Set que9or10 = CurrentDb.OpenRecordset("P_9or10ExactHC")
          que9or10.FindFirst ("PlayerID =" &amp; PlayerID)
          ExactHC = que9or10.Exact_HC
          que9or10.Close
          Set que9or10 = Nothing
      ElseIf queT20![PlayerID] &gt;= 17 Then
          Set que7or8 = CurrentDb.OpenRecordset("P_7or8ExactHC")
          que7or8.FindFirst ("PlayerID =" &amp; PlayerID)
          ExactHC = que7or8.Exact_HC
          que7or8.Close
          Set que7or8 = Nothing
      ElseIf queT20![PlayerID] &gt;= 17 Then
          Set que3to6 = CurrentDb.OpenRecordset("P_3to6ExactHC")
          que3to6.FindFirst ("PlayerID =" &amp; PlayerID)
          ExactHC = que3to6.Exact_HC
          que3to6.Close
          Set que3to6 = Nothing
      Else: ExactHC = 0
      End If
      queT20.Close
      Set queT20 = Nothing
  End Function
  </code></pre>
  
  <p>and the SQL Query Statement:</p>
  
  <pre><code>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));
  </code></pre>
  
  <p>When i try to run this i get an error message of <code>Undefined function 'EHC' in expression</code>.</p>
  
  <p>I have also tried the same query with either a IIF or SWITCH statement in replace of the function call.</p>
  
  <p>I am roughly basing the idea of the function call from the article in <a href="https://stackoverflow.com/questions/16368767/ms-access-2010-ranking-query-comparing-two-columns-for-unique-ranks?rq=1">MS Access 2010 Ranking Query comparing two columns for unique ranks</a>.</p>
  
  <p>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.</p>