title: MS-Access Get field value, run through VBA function, and send to SQL
body: <p>When calling functions from an SQL SELECT statement (Ex <code>SELECT doubLat("SELECT [Map index].[Latitude] FROM [Map index]") AS decLat FROM</code>...) how can you return the value of each item individually? I expect this is done more form the method (here in VBA) than the SQL itself.</p>
  
  <p>Note that I am not referring to changing any field values in the database itself; the goal is simply to pass a modified version into SQL for further processing. The method I currently have is shown below.</p>
  
  <pre><code>Function doubLat(strSQL As String) As Double
      Dim db As Database
      Dim rs As DAO.Recordset
      Set db = CurrentDb
  
      If (strSQL &lt;&gt; "") Then
          Set rs = db.OpenRecordset(strSQL)
          For i = 0 To rs.Fields.Count - 1 'For each field in the recordset'
              If (rs.Fields(i).Name = "Latitude") Then
                  doubLat = CDbl(LatToDecimal(rs.Fields(i).Value))
                  MsgBox (doubLat) 'debug message'
              End If
          Next i
          rs.Close
      End If
  End Function
  </code></pre>
  
  <p>LatToDecimal takes a DMS latitude value and returns a Decimal latitude value as a String. doubLat (shown above) serves as a wrapper to convert that String to a Double in order to perform further operations.</p>
  
  <p>How can individual values retrieved from such a function be passed back to an SQL query and associated (preferably temporarily) with the records that generated them if the goal is to use this data in another function later in the query?</p>
  
  <p>EDIT: Further information about the query below.</p>
  
  <p><code>SELECT</code>...<code>[Map index].[Map_Date], doubLat("SELECT [Map index].[Latitude] FROM [Map index]") AS decLat</code>
  <code>FROM [Map index]</code> <code>WHERE</code>...<code>AND (decLat BETWEEN 39 AND 41)</code></p>
  
  <p>The last condition shown (<code>decLat BETWEEN 39 AND 41</code>) is to eventually be replaced with a conditional using the haversine function and a user-defined distance.</p>
  
  <p>An example of what I am attempting to do is <a href="http://sqlexamples.info/SQL/inlineudf.htm" rel="nofollow">here</a>, with the caveat that I need to access the "new" data (output) later in the query.</p>
  
  <p>Current doubLat function is <a href="http://pastebin.com/Lv6ZyRX7" rel="nofollow">here</a>. (Edit: Error caused by lack of <code>rs.MoveNext</code> has since been corrected.)</p>
  
  <p>Final Results: As suspected, the solution was fairly simple. The <code>SELECT</code> statement needed to call <code>doubLat([Map index].[Latitude]) AS decLat</code>, where doubLat is as shown below.</p>
  
  <pre><code>Function doubLat(ByVal latIn As String) As Double
      Dim newLat As Double
      newLat = CDbl(LatToDecimal(latIn))
      doubLat = newLat
  End Function
  </code></pre>
  
  <p>Note that this solution does not yet account properly for null values in <code>[Map index].[Latitude]</code> and will set <code>decLat</code> to <code>#Error</code> for those records.</p>