Error with value nothing and error 91 object not defined when Calculating Distance between two Canadian postal code

In my access 2013 I am trying to calculate distance between two Canadian postal codes.
I have table with start and end postal codes. For several records calculation went ok and I do not have problem but when loop hit one pair of postal codes:
N3V 0A4
N5P 3L2  I have issue with error 91 object not defined. one variable showing value of "Nothing" and I can not figure out how to catch it.

Here is my code I pointed line with error:


Dim strStart_PCode As String
Dim strDestination_PCode As String
Dim strDistance_Result As String
Dim strTravel_Time As String



Set db = CurrentDb
Dim rstDist As DAO.Recordset

strSQL = "Select * from tblClient_Service_Distance"

Set rstDist = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    If rstDist.EOF = True And rstDist.BOF = True Then ' table is empty
        MsgBox "Check table tblClient_Service_Distance, No records in"
        Exit Sub
    End If
   

Dim i As Integer
Dim Rc As Integer

rstDist.MoveLast
Rc = rstDist.RecordCount
rstDist.MoveFirst

For i = 0 To Rc - 1
'we pull out value of provider postal Code
If Trim(rstDist.Fields("Service_Postal_Code")) <> "" And Trim(rstDist.Fields("Client_Postal_Code")) <> "" Then

        strStart_PCode = Trim(rstDist.Fields("Service_Postal_Code"))
        strDestination_PCode = Trim(rstDist.Fields("Client_Postal_Code"))
   
   
 
        Dim sXMLURL As String
       
       
        sXMLURL = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & strStart_PCode & "&destinations="
        sXMLURL = sXMLURL & strDestination_PCode & "&mode=driving&language=en-US&units=imperial&sensor=false"
       
        Dim objXMLHTTP As MSXML2.ServerXMLHTTP60
        Set objXMLHTTP = New MSXML2.ServerXMLHTTP60
       
        With objXMLHTTP
            .Open "GET", sXMLURL, False
            .setRequestHeader "Content-Type", "application/x-www-form-URLEncoded"
            .send
        End With



        Dim domResponse As DOMDocument60
        Set domResponse = New DOMDocument60
        domResponse.loadXML objXMLHTTP.responseText
        Dim ixnStatus
        Set ixnStatus = domResponse.selectSingleNode("//status")


            If ixnStatus.Text = "OK" Then
                    Dim ixnDistance, ixnDuration
                    Set ixnDistance = domResponse.selectSingleNode("/DistanceMatrixResponse/row/element/distance/text")
                    Set ixnDuration = domResponse.selectSingleNode("/DistanceMatrixResponse/row/element/duration/text")
               
               
                    'we will convert miles to km
               
               
                    Dim strMi As String
                    Dim dblMi As Double
               
                    Dim strKm As String
                    Dim dblKm As Double
                    Dim dblDistanceKm As Double
                   
       
                If Not IsEmpty(ixnDistance.Text) And Not IsNull(ixnDistance.Text) And Not hasNoValue(ixnDistance.Text)  Then<<<<error line
                        strDistance_Result = ixnDistance.Text
                        strMi = Left(strDistance_Result, InStr(1, strDistance_Result, " ") - 1)
       
                        dblMi = CDbl(strMi)
                        dblDistanceKm = dblMi * 1.609344
                Else
                        dblDistanceKm = 99999
                End If
     
   
   
       
                If Not IsEmpty(ixnDuration.Text) And Not IsNull(ixnDuration.Text) And Not hasNoValue(ixnDuration.Text) Then
                           strTravel_Time = ixnDuration.Text
                Else
                           strTravel_Time = "No Duration Time"
                End If
       


        End If



            rstDist.Edit            
           
                rstDist.Fields("Distance") = dblDistanceKm
               
                If Nz(strTravel_Time, "") = "" Or strTravel_Time = "" Then
                    rstDist.Fields("Travel_Duration") = ""
                Else
                    rstDist.Fields("Travel_Duration") = strTravel_Time
                End If
           
            rstDist.Update
           

       
   


Else
      rstDist.Edit
     
     
      rstDist.Fields("Distance") = 0
      rstDist.Fields("Travel_Duration") = "N/A"
   
     
      rstDist.Update
     
 



End If
    rstDist.MoveNext
    Next i
   

    rstDist.close
    set rstDist = Nothing

    Set domResponse = Nothing
    Set objXMLHTTP = Nothing
TarasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
 If Not IsEmpty(ixnDistance.Text) And Not IsNull(ixnDistance.Text) And Not hasNoValue(ixnDistance.Text)  Then<<<<error line
hasNoValue is not a VBA function, so we can't determine if that is causing your troubles.

Personally I'd use separate IF statements to check the values, for example:

If Not ixnDistance Is Nothing Then
    If Not IsNull(ixnDistance) Then
      If Not IsNull(ixnDistance) Then
      End If
    Else
    End If
Else

End If

I'm not sure you need all those checks, of course. Generally speaking, if the Not … Is Nothing returns true, you're good to go.
0
Dale FyeCommented:
The Trim( ) function will fail if the value passed is NULL, so I would start out by changing:

If Trim(rstDist.Fields("Service_Postal_Code")) <> "" And Trim(rstDist.Fields("Client_Postal_Code")) <> "" Then 

Open in new window

to
If Trim(rstDist!Service_Postal_Code & "")) <> "" And Trim(rstDist!Client_Postal_Code & "")) <> "" Then

Open in new window

Note: you don't have to use the Fields property of the recordset to refer to a column, simply use rst!ColumnName or rst![Column name].

I'm not sure that you need the IsEmpty( ) test in this line:
If Not IsEmpty(ixnDistance.Text) And Not IsNull(ixnDistance.Text) And Not hasNoValue(ixnDistance.Text)  Then

Open in new window

Try replacing that line with:
If Len(Trim(ixnDistance.Text & "")) > 0 Then

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
I am using Function from Roger G. Abbott.

Public Function hasNoValue(myExpression As Variant) As Boolean
    ' Author: Roger G. Abbott      Date: 11-Jan-2013

    Dim v       As Variant
   
    On Error GoTo Err_Handler
   
    hasNoValue = False        ' not true unless we see the error!
   
    If myExpression Is Nothing Then GoTo exit_Main
    If IsNull(myExpression) Then GoTo exit_Main
    If IsEmpty(myExpression) Then GoTo exit_Main
   
    ' the assignment statement below will trigger error 2427 if the expression has no value.
    v = myExpression.Value
   
    GoTo exit_Main
Err_Handler:
    If Err.Number = 2427 Then
        hasNoValue = True
    Else
        hasNoValue = False
        ' we should not get any other type of error here!
'        Call LogMsg("E:", "isExpressionHasNoValue()", "Unexpected error number: " & Err.Number, "")
    End If
    Resume exit_Main
exit_Main:
End Function
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The remove all the other checks and just use HasNoVAlue. It accepts a Variant, which can be NULL.
0
TarasAuthor Commented:
Dale those variable after trim function have  values
strStart_PCode = Trim(rstDist.Fields("Service_Postal_Code"))
strDestination_PCode = Trim(rstDist.Fields("Client_Postal_Code"))
Values are passed to this part:
  sXMLURL = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & strStart_PCode & "&destinations="
         sXMLURL = sXMLURL & strDestination_PCode & "&mode=driving&language=en-US&units=imperial&sensor=false"

but when they hit this part they have nothing:
 Set ixnDistance = domResponse.selectSingleNode("/DistanceMatrixResponse/row/element/distance/text")
Set ixnDuration = domResponse.selectSingleNode("/DistanceMatrixResponse/row/element/duration/text")
0
TarasAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.