Query code generating Invalid Use Of Null

Question

My code is generating an Invalid Use Of Null and I am not seeing the issue. When I compile the code, I do not get an error, but when I run and debug it, the error occurs at strRESMILE = rs("RESMILE").

Any thoughts? I can upload the database if need be.

    Sub COMPARE()
On Error GoTo err_COMPARE

 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim strSQL As String
 Dim strHold As String
 Dim strRESMILE As String
 Dim strRESMONTH As String
 Dim dblMILEAGEHold As Double
 Dim dblDATEHold As Double

 Set db = CurrentDb
 strSQL = "SELECT * FROM TABLE3"
 Set rs = db.OpenRecordset(strSQL)
 With rs
' If Not .BOF And Not .EOF Then
'        .MoveLast
'        .MoveFirst
    If Not .BOF Then
        strHold = rs("VIN")
        dblMILEAGEHold = rs("MILES")
        .Edit
        rs("RESMILE") = ""
        .Update
        .MoveNext
'While (Not .EOF)
        Do Until .EOF
            .Edit
            If rs("VIN") = strHold Then
                'do comparison
              If rs("MILEAGE") > rs("MILES") Then
                 rs("RESMILE") = "Y"
            Else
                rs("RESMILE") = "N"
              End If
            End If
            .Update
            strHold = rs("VIN")
            strRESMILE = rs("RESMILE")
            .MoveNext
'            Wend
        Loop
    End If
 End With

 Set db = CurrentDb
 strSQL = "SELECT * FROM TABLE3"
 Set rs = db.OpenRecordset(strSQL)
 With rs
    If Not .BOF Then
        strHold = rs("VIN")
        dblDATEHold = rs("MONTHS")
        .Edit
        rs("RESMONTH") = ""
        .Update
        .MoveNext
        Do Until .EOF
            .Edit
            If rs("VIN") = strHold Then
                'do comparison
              If rs("INSM") > rs("MONTHS") Then
                 rs("RESMONTH") = "Y"
            Else
                rs("RESMONTH") = "N"
               End If
            End If
            .Update
            strHold = rs("VIN")
            dblDATEHold = rs("RESMONTH")
            .MoveNext
        Loop
    End If
End With

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

MsgBox "Comparisons Completed!"

exit_COMPARE:
    Exit Sub

err_COMPARE:
    MsgBox Err.Description
    Resume exit_COMPARE

End Sub

JCro · Accepted Answer · 2016-09-28 23:00:22Z

1

Handle the evident NULL in your [RESMILE] field by using Nz().

So change strRESMILE = rs("RESMILE") to strRESMILE = Nz(rs("RESMILE"))

answered Sep 28, 2016 at 23:00

JCro

6965 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

casaler Over a year ago

That worked for the rs("RESMILE") and code went through first loop. I applied Nz to rs("RESMONTH"), executed code and now get a Type Mismatch error when executing the second loop. Thoughts? The data types are the same for RESMILE and RESMONTH.

casaler Over a year ago

Scratch my previous comment. I just realized that I had rs("RESMONTH") equaling dblDATEHOLD which is not a string; changed to strRESMONTH and code ran with no errors. Can you tell me why the code does not check the first entry of the VIN hold? That is, if the VIN has 3 entries, only entries 2 & 3 are checked.

casaler Over a year ago

Can you tell me why the code does not check the first entry of the VIN hold? That is, if the VIN has 3 entries, only entries 2 & 3 are checked.

JCro Over a year ago

It looks like you .movenext the first record, then loop the remainder.

If Not .BOF Then         strHold = rs("VIN")         dblDATEHold = rs("MONTHS")         .Edit         rs("RESMONTH") = ""         .Update         .MoveNext         Do Until .EOF

Collectives™ on Stack Overflow

Query code generating Invalid Use Of Null

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related