Type mismatch error... (1 Viewer)

[ThierryA]

[Solved] Type mismatch error...

Hi everyone !

I'm totally stumped by what is probably a stupid mistake on my part, but I have been on it since this morning and I cannot get over it...:banghead:

I'm calling several function with this code:

Private Sub GetThisPictureStationName_Click()
    Dim MyLat As Double
    Dim MyLon As Double
    Dim ThisDistance As Double
    Dim ThisLoc As Integer
    
    MyLat = CDbl([T_Pictures.lat])
    MyLon = CDbl([T_Pictures.lon])
    
    ThisLoc = AutoLocate(MyLat, MyLon)
    ThisDistance = Distance(MyLat, MyLon)
    MsgBox GetStationName(ThisLoc) & " = " & ThisDistance
End Sub

First call: ThisLoc = AutoLocate(MyLat, MyLon), works perfectly.
Second one: ThisDistance = Distance(MyLat, MyLon), throws the type mismatch error. Input data are strictly the same and the code is mostly a copy-paste of the first one.

Parameters are a longitude and a latitude. The first function returns the code of the closest entry found in the table.
The second should return the value of the "distance" between given point and closest found.

First function (which works):

Public Function AutoLocate(MyLat As Double, MyLon As Double) As Double

Dim MinDistance As Double
Dim ThisDistance As Double
Dim Thislat As Double
Dim ThisLon As Double
Dim ThisStation As Integer
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    MinDistance = 1E+100
    ThisStation = 0
    Set rs = db.OpenRecordset("select * from T_Geoloc")
    rs.MoveFirst
    Do While Not rs.EOF
        Thislat = rs.Fields("lat")
        ThisLon = rs.Fields("lon")
        ThisDistance = Sqr(((Thislat - MyLat) ^ 2) + ((ThisLon - MyLon) ^ 2)) * 100
        If ThisDistance < MinDistance Then
            MinDistance = ThisDistance
            ThisStation = rs.Fields("Code")
        End If
    rs.MoveNext
    Loop
    rs.Close
    'Est-on dans le "fuzziness radius" de la station ?
    'Set rs = db.OpenRecordset("select * from T_Geoloc where Code=" & ThisStation)
        'If MinDistance > rs.Fields("fuzziness") Then ThisStation = 0
    'rs.Close
AutoLocate = ThisStation
End Function

Secund function (which doesn't):

Public Function Distance(MyLat As Double, MyLon As Double) As Double

Dim MinDistance As Double
Dim ThisDistance As Double
Dim Thislat As Double
Dim ThisLon As Double
Dim ThisStation As Integer
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    MinDistance = 1E+100
    ThisStation = 0
    Set rs = db.OpenRecordset("select * from T_Geoloc")
    rs.MoveFirst
    Do While Not rs.EOF
        Thislat = rs.Fields("lat")
        ThisLon = rs.Fields("lon")
        ThisDistance = Sqr(((Thislat - MyLat) ^ 2) + ((ThisLon - MyLon) ^ 2)) * 100
        If ThisDistance < MinDistance Then
            MinDistance = ThisDistance
            ThisStation = rs.Fields("Code")
        End If
    rs.MoveNext
    Loop
    rs.Close
Distance = MinDistance
End Function

Last one (which also works fine, just tu be complete):

Public Function GetStationName(CodeLoc As Integer) As String
'renvoie le nom d'une station sur la base de son code
If CodeLoc > 0 Then
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("select * from T_Geoloc where Code=" & CodeLoc)
    GetStationName = rs.Fields("NomLieu")
    rs.Close
Else
    GetStationName = "Station non déterminable"
End If
End Function

Help please !

 

[namliam]

it is good practice to make sure and disambiguate...
DAO.Database
DAO.Recordset

At what point is the mismatch error thrown?

 

[JHB]

I don't know if it is this, but you have declared ThisStation As Integer and MinDistance As Double and in the function which work you set the function to ThisStation, and the other to MinDistance.
Another thing could be that, in the one function the If statement is triggered and in the other not, put in a breakpoint and step through the code.
The If statement is this one:

If ThisDistance < MinDistance Then

 

[ThierryA]

Ok I have this figured out !

First to answer the questions: the error is thrown before entering the function.
About ThisStation and ThisDistance, you are right, but that was only a test I had made when looking for the source of the error and it was not the way the code was supposed to be (silly of me to send this)

Well, as usual , the error was far more simple and far more stupid than I thought it to be: I already had something named Distance in my code.
On a hunch I renamed my function to AutoDistance, and voila ! It worked...

Sometimes I feel like :banghead::banghead::banghead:

Thanks !