Data Type Conversion Error - WHY?

[Datamulcher]

Hey awesome people!



I'm working in a database of GIS data (a large "big data"-database), currently trying to determine why our front-end Access database used by the Coders is experiencing ODBC call failures when accessing data from an SQL back-end database housing the data.

One possible reason for the ODBC call failures is that there is malformed data in one of the tables, the SQL server can handle the data formats since it doesn't care, but Access is touchy..
We had new computers as of a month ago, and an upgraded fiber-optic connection network-wide so I am hoping it is not a physical issue of the data not being throughput.

Anyways- assuming malformed data in a table, we found the most likely suspect as one table which is always being called. I started looking for the prime candidate (from past experience), which is malformed latitude longitudes in the lat/long field. I.e. the data should be in the format like "44.2716497,-78.1696004" aka "##?#####??##?######" or:
2 numbers, a period, more numbers, a comma a dash and 2 numbers, a period, more numbers.

So, with some help from you amazing people months ago, I cobbled together this code which looks for latlong fields that contain anything other than numbers/commmas/dots/dashes:

Function numcommadot2(sIn As String) As String
    Dim c As String, i As Integer
    On Error GoTo numcommadot_Error
    sIn = Trim(sIn)
    Dim sHold As String
    For i = 1 To Len(sIn)
        c = Mid(sIn, i, 1)
        If InStr("0123456789-,.", c) <> 0 Then
            sHold = sHold & c
        Else
        End If
    Next
    
    On Error GoTo 0
    numcommadot2 = sHold
    Exit Function

numcommadot_Error:

    MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure numcommadot of Module AWF_Related"
End Function

Public Sub DelNonPrintable()

    Dim r As DAO.Recordset
    Dim sSQL As String
    Dim sTmp As String

    sSQL = "SELECT DestLat FROM tbl_2016_XXX_Trips"
    Set r = CurrentDb.OpenRecordset(sSQL)

    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst

        Do While Not r.EOF
            sTmp = vbNullString
            sTmp = r("DestLat") & ""
            sTmp = numcommadot2(sTmp)

            r.Edit
            r("DestLat") = sTmp
            r.Update
            r.MoveNext
        Loop

    End If
End Sub

I am getting a data type conversion error at the red text - I have NO idea why. It is a string already defined as such..
So- does anyone know why this is failing??

Also, is there a better way to do this?? To make a table of all the latlong fields with invalid characters?

Thanks in advance, you guys are always great!

[Bulzie]

What is the value in sTmp? Also maybe change the code to r!DestLat = sTmp

Make sure DestLat is actually a string value in the table. If it is could do r!DestLat = Cstr(sTmp)

[Micron]

I've never referenced a recordset field that way r("DestLat") as opposed to r.Fields(""DestLat"). Not sure why you're setting a variable to vbNullString right before applying a value of some sort. I would msgbox or debug.print at this point r("DestLat") = sTmp to see what sTmp is. If it's not null, I'd check it's data type the same way. As for a better way - I envision you're passing a field value to the function and looping through its length. If all characters pass the test, return the field back to the sub and update the table field.

What I don't see it doing is flagging a field value that doesn't pass the test, so I don't see how you're creating a list of non-compliant records. For that, I think I'd make the function return a Boolean. If the test <> zero then False else True (or the reverse if you prefer - the name of the function doesn't support a clear understanding of which is which). Then either write the value to the table or flag it with some sort of character precedent, or have a field you use to flag as OK or not and edit it accordingly. I also don't see why you're editing the field with a value that you already had in it if it passes the test.
simplified example

Do While Not r.EOF
  If numcommadot2(r.Fields("DestLat")) = False Then 'assuming false means invalid characters exist
   'flag this or some other field
  Else
   r.MoveNext
Loop