Checking Null Value in Access VBA recordset throws null exception?

Question

I have tried this every different way, and it was working yesterday, so I really don't know what changed.

I import a spreadsheet to a temp table in an Access app. Then I set that to be the dao.recordset, and start looping through. I check for the ID to not be null and if not go through checking fields for values, and updating as appropriate. the minute I hit a null, I get a system error "94 - invalid use of null"

It doesn't offer a debug, but I have debugs throughout my code, so I can see where it fails. It fails when I do this check: If IsNull(rstImportCList("columnx")) = False Then

I have tried nz(rstImportCList("columnx"),"") <> "" I have tried rstImportCList("columnx") is not null, and everything else I can think of. Why is the check that is supposed to prevent this error, causing this error?

Edit:

This is the beginning where I declare the recordset I can't get past doing anything with the recordset field.

Dim db As DAO.Database
Dim rstImportCList As DAO.Recordset
Dim RSsql As String
Set db = CurrentDb()
    RSsql = "Select * from tblTempImportCList"
    Set rstImportCList = db.OpenRecordset(RSsql)
    If rstImportCList.EOF Then Exit Sub
            
    Do While Not rstImportCList.EOF

whether I try to check

IsNull(rstImportCList("xyz").Value) = False

or

nz(rstImportCList("xyz").Value,"") <> ""

or

dim x as string 
x = rstImportCList!xyz.value

I get the same error 94 invalid use of null.

Any idea why this is? --edit with more code.

I took some time to take a the beginning and some of each section of the code, so I could make it generic and see if anyone can help. Here is what I am working on. The Code1 and Code2 parts don't seem to be the issue. Sometimes it fails on a null value in a Yes/No column (I'm just looking for Y but the value is null), sometimes on the notes being null. It's not consistent, which is why I'm having a hard time nailing down the issue.

Private Sub cmdImportList_Click()
On Error GoTo cmdImportExcel_Click_err:
Dim fdObj As FileDialog
Set fdObj = Application.FileDialog(msoFileDialogFilePicker)
Dim varfile As Variant
Dim importCT As Integer
Dim dbu As DAO.Database
Dim cBadXVal, cBadYVal As Integer
Dim preNotes As String
Dim RSsql As String
Dim uNotesql, uVal1sql, uVal2sql As String
Dim db As DAO.Database
Dim rstImportCList As DAO.Recordset
Dim CheckB4Import As Integer


CheckB4Import = MsgBox("Are you SURE the sheet you are importing has the following column names in the same order:" & vbCrLf & vbCrLf & _
"IDName/ First/ Mid/ Last/ Sfx/ Age/ Telephone/ Code1/ Code2/ YN1/ YN2/ NY3/ Notes/ AsYN1edTo" & vbCrLf & vbCrLf & _
"AND that there are NO empty rows or empty columns?" & vbCrLf & vbCrLf & _
"Click OK to proceed, Click CANCEL to go double-check your CallSheet before importing.", vbOKCancel, "WITH GREAT POWER COMES GREAT RESPONSIBILITY TO QC DATA")

If CheckB4Import = vbOK Then

    CurrentDb.Execute "DELETE * FROM tblTempImportCList", dbFailOnError
    With fdObj
        'CAN ONLY SELECT 1 FILE
        .allowmultiselect = False
        .Filters.Clear
        .Filters.Add "Excel 2007+", "*.xlsx"
        .Title = "Please select the completed list to import:"
        .Show
    
        If .SelectedItems.Count = 1 Then
            varfile = .SelectedItems(1)
            
            DoCmd.TransferSpreadsheet acImport, , "tblTempImportCList", varfile, True, "Sheet1!"
            
            cBadXVal = DLookup("BadXCount", "qryImpCheckBadXVal")
            Debug.Print "cBadXVal - " & cBadXVal
            If cBadXVal <> 0 Then
                DoCmd.OpenForm "frmImportError", acNormal
                Forms!frmImportError.Form.lblErrorMsg.Caption = _
                    "Oh No! Your list import failed!" & vbCrLf & _
                    cBadXVal & " X values are not valid." & vbCrLf & _
                    "Don't worry. You can fix your sheet and re-import!" & vbCrLf & _
                    "Would you like to open the documentation for the valid codes" & vbCrLf & _
                    "Or are you all set?"
            End If
            
            cBadYVal = DLookup("BadYCount", "qryImpCheckBadYVal")
            Debug.Print "cBadYVal - " & cBadYVal
            If cBadYVal <> 0 Then
                DoCmd.OpenForm "frmImportError", acNormal
                Forms!frmImportError.Form.lblErrorMsg.Caption = _
                    "Oh No! Your list import failed!" & vbCrLf & _
                    cBadYVal & " YN1 values are not valid." & vbCrLf & _
                    "Don't worry. You can fix your sheet and re-import!" & vbCrLf & _
                    "Would you like to open the documentation for the valid codes" & vbCrLf & _
                    "Or are you all set?"
            Exit Sub
            End If
            
        Else
            MsgBox "No file was selected. Try again!", vbCritical, "Uh-oh Spaghettios!"
        End If
    End With
    
 
'PASSED CHECKS
    Set db = CurrentDb()
    RSsql = "Select * from tblTempImportCList"
    Set rstImportCList = db.OpenRecordset(RSsql)
    If rstImportCList.EOF Then Exit Sub
            Debug.Print "got here"
    Do While Not rstImportCList.EOF
    
        Debug.Print "Start Processing: " & Nz(rstImportCList("IDName").Value, "")
        
        'GET NOTES ALREADY ON RECORD
        If Nz(rstImportCList("IDName").Value, "") <> "" Then
            Debug.Print "got past if IDName is not null"
            If Nz(rstImportCList("Notes").Value, "") <> "" Then
            Debug.Print "got past if notes is not null"
                preNotes = Replace(Nz(DLookup("Notes", "tblVFileImport", "IDName = " & rstImportCList("IDName").Value), ""), """", "")
                'UPDATE NOTES
                If Nz(preNotes, "") <> "" Then
                    uNotesql = "Update tblVFileImport SET tblVFileImport.Notes = '" & preNotes & "; " & Replace(Nz(rstImportCList("Notes").Value, ""), """", "") & "' " & _
                    "WHERE tblVFileImport.IDName = " & rstImportCList("IDName").Value
                    'debug.print "Notes"
                    'debug.print "uNotesql - " & uNotesql
                Else
                    uNotesql = "Update tblVFileImport SET tblVFileImport.Notes = '" & Replace(Nz(rstImportCList("Notes").Value, ""), """", "") & "' " & _
                    "WHERE tblVFileImport.IDName = " & rstImportCList("IDName").Value
                End If
                RunMySql (uNotesql)
                'DoCmd.RunSQL (uNotesql), dbFailOnError
            End If
        
            
            If Nz(rstImportCList("YN1").Value, "") = "Y" Then
                'UPDATE YN1
                uYN1sql = "Update tblVFileImport SET tblVFileImport.YN1 = '" & rstImportCList("YN1") & "', tblVFileImport.callprocessed = 'Y' " & _
                "WHERE tblVFileImport.IDName = " & rstImportCList("IDName")
                Debug.Print "YN1 = Y or y"
                Debug.Print "uYN1sql - " & uYN1sql
                RunMySql (uYN1sql)
                'DoCmd.RunSQL (uYN1sql), dbFailOnError
            End If

            If Nz(rstImportCList("YN2").Value, "") = "Y" Then
                'UPDATE YN2
                uYN2sql = "Update tblVFileImport SET tblVFileImport.YN2 = '" & rstImportCList("YN2") & "', tblVFileImport.callprocessed = 'Y' " & _
                "WHERE tblVFileImport.IDName = " & rstImportCList("IDName")
                Debug.Print "YN2 = Y or y"
                Debug.Print "uYN2sql - " & uYN2sql
                RunMySql (uYN2sql)
                'DoCmd.RunSQL (uYN2sql), dbFailOnError
            End If

'START Code1 PROCESSING

            If Nz(rstImportCList("Code1").Value, "") <> "" Then
'Code1 Case abc
                vdispo = DLookup("Code1", "tblvFileImport", "IDName = " & rstImportCList("IDName"))
                
                If rstImportCList("Code1") = "ABC" Then
                    Debug.Print "Dispo Case ABC"
                    'DELETE RECORD
                    dMDsql = "DELETE from tblVFileImport " & _
                    "WHERE tblVFileImport.IDName = " & rstImportCList("IDName")
                    Debug.Print "dMDsql - " & dMDsql
                    RunMySql (dMDsql)
                    'DoCmd.RunSQL (dMDsql), dbFailOnError

'Code1 Case DEF OR GHI OR JKL 
                ElseIf Nz(rstImportCList("Code1"), "") = "DEF" Or Nz(rstImportCList("Code1"), "") = "GHI" Or Nz(rstImportCList("Code1"), "") = "JKL" Then
                    Debug.Print "Dispo Case DEF OR GHI OR JKL "
                    'IF DEF
                    If rstImportCList("Code1") = "DEF" Then
                        'IF CELL SAME - UPDATE NULL
                        ccellsame = DLookup("IDName", "tblVFileImport", "IDName = " & rstImportCList("IDName") & " AND nz(Cell,'') = Phone ")
                        If ccellsame = rstImportCList("IDName") Then
                            uCellsql = "Update tblVFileImport SET tblVFileImport.Cell = NULL, tblVFileImport.CellString = NULL, tblVFileImport.mobileflag = NULL " & _
                            "WHERE tblVFileImport.IDName = " & rstImportCList("IDName")
                            Debug.Print "uCellsql - " & uCellsql
                            RunMySql (uCellsql)
                            'DoCmd.RunSQL (uCellsql), dbFailOnError
                        End If
                    End If
                End If
            End If
        End If
        Debug.Print "End Processing: " & rstImportCList("IDName")
        rstImportCList.MoveNext
    Loop

    Debug.Print "Finished Looping"
    rstImportCList.Close
    
    importCT = DCount("IDName", "tblTempImportCList")
    MsgBox importCT & " Records imported for list.", vbOKOnly, "List Processed"
Else
    MsgBox "Good Call. Check twice, import once!", vbOKOnly, "Better Safe Than Sorry"
End If
Exit Sub

cmdImportExcel_Click_err:
Select Case Err.Number
Case Else
Call MsgBox(Err.Number & " – " & Err.Description, vbCritical + vbOKOnly, "System Error …")
End Select
End Sub

Any suggestions are greatly appreciated. I'm 1/2 tempted to suck this into a SQL table and just execute a stored procedure. I can get it to work in there, I think.

We need to see how you are implementing the DAO recordset lines like [enum types and options](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-openrecordset-method-dao). Please post some code for [mcve]. — Parfait, Jun 14 '21 at 21:07
According to https://stackoverflow.com/questions/664162/how-can-you-check-for-null-in-a-vba-dao-record-set there should be no problem testing for `IsNull(rstImportCList("xyz").Value)` Interesting there's no Debug option though. — Tim Williams, Jun 14 '21 at 22:20
Try switching to "Break in Class module" for error handling, if that's not already selected. — Tim Williams, Jun 14 '21 at 22:30
I switched it back to this Nz(rstImportCList("xxx").Value, "") <> "" and for some reason it's now working. — missscripty, Jun 14 '21 at 22:57
I take that back. It's failing, but inconsistently. I have checks of each field value in the recordset table, where I check if the nz(x,"") = "something" or <>"" like this Nz(rstImportCList("xxx").Value, "") = "Y" . My debug messages show it looping through records where that is null, with no problem, but then one record, I'm getting the invalid use of null. tried a different list, and it's still happening, but not in the same place. — missscripty, Jun 15 '21 at 00:44
Have you tried fixing your Error settings so you get a Debug option? That's going to be a quicker way to figure out exactly what the problem is. — Tim Williams, Jun 15 '21 at 01:04
I already have this in there: On Error GoTo cmdImportExcel_Click_err: xxxcode cmdImportExcel_Click_err: Select Case Err.Number Case Else Call MsgBox(Err.Number & " – " & Err.Description, vbCritical + vbOKOnly, "System Error …") End Select should I have something different? — missscripty, Jun 15 '21 at 01:15
Comment out the `On Error Goto cmdImportExcel_Click_err` so you can get to the `Debug` option when an error occurs - that should be more productive than relying on Debug.Print messages. — Tim Williams, Jun 15 '21 at 01:51
What are you actually trying to do? `nz(rstImportCList("xyz").Value,"") <> ""` doesn't really make sense, because it's the same as `rstImportCList("xyz").Value <> ""` if you use it in an `If` statement — Erik A, Jun 15 '21 at 06:23
If `rstImportCList("xyz").Value` is `Null` then you can't use `<>""` as a test, because `Null <> ""` evaluates to Null (as does `Null = ""`) — Tim Williams, Jun 15 '21 at 06:36
Only the last check will fail in case of _Null_ value, so something is missing or something else is going on. — Gustav, Jun 15 '21 at 06:44
Maybe do a loop over each field and print out the field name, value, and the `IsNull` test, might be helpful to spot the issue. You can access fields via the `rs.Fields("MyFieldName")` method too. Lastly, I see a perhaps unrelated bug. When you display this `MsgBox "No file was selected. Try again!", vbCritical, "Uh-oh Spaghettios!"` you aren't exiting, which means the code would try and process the data if a file wasn't selected. — Ryan Wildry, Jun 15 '21 at 12:00
Interesting. I decided to scrap 1/2 of this. I am going to have them select the file, and then check that the columns with codes all have only valid codes. Once that passes, I am going to execute a stored procedure and do the rest of this in SQL. The stored proc will just run a series of updated statements, accounting for every code scenario (grouped by scenarios that are handled the same). One thing, and this may be for a different post. Is there an easy way to write an insert into a log table, along with each import, to log what I did, in case we run into a conflict? — missscripty, Jun 16 '21 at 19:51

Tim Williams · Answer 1 · 2021-06-14T21:40:46.867

0

If IsNull(rstImportCList("columnx").Value) Then

otherwise you're checking if the Field object itself is null.

https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/field-object-dao#:~:text=To%20refer%20to,Fields(%22name%22)

This is a case where relying on a default property (in this case Value) causes problems.

edited Jun 14 '21 at 21:40

answered Jun 14 '21 at 21:13

Tim Williams

154,628
8
97
125

So I tried Nz(rstImportCList("xxx").Value, "") = "Y" and that still throws the error. If I add a layer to do a null check I need a not null to then check for the Y . I don't get why this is not handling a null better. – missscripty Jun 14 '21 at 21:38
It would help your question a lot if you included the actual code you're using. – Tim Williams Jun 14 '21 at 21:40
I will if I get a minute, but it is a lot of code, and I'm in the weeds and under a time crunch, so just keep looking for any way out of this stupid issue. – missscripty Jun 14 '21 at 21:48
You only need to show the lines around where the problem is. You're more likely to get useful responses that way. – Tim Williams Jun 14 '21 at 21:52

Checking Null Value in Access VBA recordset throws null exception?

1 Answers1