CODEWITHSUNDEEP
ms-accessvba
Nathaniel Bendinsky
Nathaniel Bendinsky

Reputation: 143

Access-VBA Non-Error Values Displaying as #Error

I am attempting to handle errors caused by blank fields in an Access database. I have a function that takes the Latitude field's value, parses it to another format (from DMS to decimal), and is used in a query as a new field. As long as Latitude contains a valid DMS value, this works as expected. However, if it does not, the new field (decLat) will contain #Error.

From my readings, this is typically caused by the value passed in being an Error itself. However, testing with IsError(doubLat) in the function itself shows that none of the outputs are Error.

My next attempt was to check whether the output was 0 (because the conversion function is meant to return that in event of an error), but that doesn't seem to be the issue either. So the question is, what causes the code below to return #Error when the input is null?

Function LatToDecimal(latIn As Variant) As String 'latIn is Variant as attempt at error handling. Will always be String or Null.
    On Error GoTo ErrHandler
    If (latIn = "") Then
        GoTo ErrHandler
    ElseIf (IsNull(latIn)) Then GoTo ErrHandler
    End If
Restart:
    Dim SplitA() As String
    SplitA() = Split(latIn, "°") 'A(0) is DEG.
    Dim SplitB() As String
    If (UBound(SplitA) > 0) Then
        SplitB() = Split(SplitA(1), "'") 'B(0) is MIN.
    Else:
        LatToDecimal = "0"
        Exit Function
    End If
    Dim SplitC() As String
    If (UBound(SplitB) > 0) Then
        SplitC() = Split(SplitB(1), """") 'C(0) is SEC. C(1) is N/S.
    Else:
        LatToDecimal = "0"
        Exit Function
    End If
    Dim deg As Double
    Dim min As Double
    Dim sec As Double

    deg = CDbl(SplitA(0)) 'Assign more readable names.
    min = CDbl(SplitB(0))
    sec = CDbl(SplitC(0))

    Dim minM As Double 'Transitional value.
    Dim decDeg As Double 'decimal degrees

    'Conversion to decimal
    minM = min + (sec / 60)
    decDeg = deg + (minM / 60)
    If (SplitC(1) = "S") Then decDec = decDeg * -1

    'Return value
    LatToDecimal = decDeg
    GoTo Finished

ErrHandler:
    latIn = "0°0'0"""
    MsgBox ("Error!")
    GoTo Restart
Finished:
End Function

'Wrapper to convert decimal latitude (from LatToDecimal) into Double values. Still need one for longitude.
Function doubLat(ByVal latIn As String) As Double
    Dim newLat As Double
    newLat = CDbl(LatToDecimal(latIn))
    doubLat = newLat
    If (doubLat = 0) Then doubLat = 0#
End Function

The function doubLat is the one called by my query.

Upvotes: 1

Views: 765

Answers (1)

HansUp
HansUp

Reputation: 97100

what causes the code below to return #Error when the input is null?

Your query uses your doubLat() function:

Function doubLat(ByVal latIn As String) As Double

If you called that function with Null in an Immediate window session, Access would throw error #94, "Invalid use of Null". The error handling you included within the function does not come into play because the function does not even run. (By the way, checking IsNull(latIn) is pointless because you declared latIn As String ... and that means latIn can never be Null.)

When you use that function as a query field expression and give it Null, Access just displays #Error in the query result set.

Avoid that problem by substituting something else (an empty string?) for Null when you call the function ...

SELECT doubLat(IIf(YourField Is Null, '', YourField))

Or you could use the VBA Nz() function ...

SELECT doubLat(Nz(YourField, ''))

Upvotes: 1

Related Questions