CODEWITHSUNDEEP
sqlms-accessvba
Nathaniel Bendinsky
Nathaniel Bendinsky

Reputation: 143

MS-Access Get field value, run through VBA function, and send to SQL

When calling functions from an SQL SELECT statement (Ex SELECT doubLat("SELECT [Map index].[Latitude] FROM [Map index]") AS decLat FROM...) 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.

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.

Function doubLat(strSQL As String) As Double
    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb

    If (strSQL <> "") 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

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.

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?

EDIT: Further information about the query below.

SELECT...[Map index].[Map_Date], doubLat("SELECT [Map index].[Latitude] FROM [Map index]") AS decLat FROM [Map index] WHERE...AND (decLat BETWEEN 39 AND 41)

The last condition shown (decLat BETWEEN 39 AND 41) is to eventually be replaced with a conditional using the haversine function and a user-defined distance.

An example of what I am attempting to do is here, with the caveat that I need to access the "new" data (output) later in the query.

Current doubLat function is here. (Edit: Error caused by lack of rs.MoveNext has since been corrected.)

Final Results: As suspected, the solution was fairly simple. The SELECT statement needed to call doubLat([Map index].[Latitude]) AS decLat, where doubLat is as shown below.

Function doubLat(ByVal latIn As String) As Double
    Dim newLat As Double
    newLat = CDbl(LatToDecimal(latIn))
    doubLat = newLat
End Function

Note that this solution does not yet account properly for null values in [Map index].[Latitude] and will set decLat to #Error for those records.

Upvotes: 2

Views: 12234

Answers (1)

Don George
Don George

Reputation: 1328

rs.fields("Lattitude").value will give you the contents of that field - is that what you mean? As far as associating it with a specific record, I usually have a loop something like

set rs= ...
rs.movefirst
do while not rs.eof 
   ...
   Lattidue=rs.fields("Lattitude").value
   ...
   rs.movenext
   loop

This deals with one record of the recordset at a time, so you can associate the Lattitude to any other fields of the same record

Upvotes: 1

Related Questions