Remove Nulls in all Fields in MS Access Database

Using the code I found in the newsgroups, I also had a need to remove all Nulls from every table in my databases.

For some reason, using ADO to pull records from Access into Excel was causing problems when Nulls were present. So I need to remove them from every field of every table in my database. Well, I don't need to, but it's easier to loop through every table than to run this code on select tables.

So here's the code I used (in MS Access 2003). It loops through every table, acting on only non- linked or system tables. For each field in each table, it checks the data type and sets the appropriate default value.

Sub GetRidOfNullsAllTables()
  Dim db As DAO.Database
  Dim tbls As DAO.TableDefs
  Dim tbl As DAO.TableDef
  Dim thisTable As DAO.TableDef
  Dim SQLString As String
  Dim flds As DAO.Fields
  Dim fld As DAO.Field

  Set db = CurrentDb
  Set tbls = db.TableDefs

  ' loop through each appropriate table (i.e. no linked or system tables)
  For Each tbl In tbls
    If tbl.Attributes = 0 Then
      Set thisTable = tbl
      ' grab all fields
      Set flds = thisTable.Fields

      For Each fld In flds

        SQLString = "UPDATE [" & tbl.Name & "] SET "

        ' check field type and set to zero or empty string as appropriate
        Select Case fld.Type
          Case dbText
            SQLString = SQLString & "[" & fld.Name & "] = """" WHERE [" & fld.Name & "] Is Null;"
          Case dbDouble, dbInteger, dbLong, dbBigInt, dbDecimal
            SQLString = SQLString & "[" & fld.Name & "] = 0 WHERE [" & fld.Name & "] Is Null;"
        End Select

        Debug.Print SQLString
        ' execute update statement on field
        db.Execute SQLString, dbFailOnError
      Next fld

    End If
  Next tbl

End Sub

I know what you're thinking: where are the other data types? I don't really use them. Everything in my database is either text, or one of the basic number types. I do have some Date fields, but I don't want to touch those because there's no value I can set them to that will make them appear as anything other than dates (I'd be glad to hear otherwise), which will get them confused with the legit data. So I'm just fine with them staying Null.

You might also be asking why I'm converting numbers to zeroes, after all, isn't zero a number? In my database, zero doesn't matter since it doesn't add to the sum of anything. Trust me, it doesn't matter to my database.

Doing this, along with 'Compact and Repair' and Performance Analyzer have really served to speed up my database.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 8 Comment(s) on Remove Nulls in all Fields in MS Access Database:

  1. May I know why ADO is causing problems? Most of the time, I use ADO to connect database from Excel.

    • You'll have to be more specific than that.

      What version of Office?
      What version of ADO are you using?
      What are you trying to do?
      Where is the code housed (Access? Excel?)
      What is the code that causes the error?
      What error message are you seeing?

      • No. I dont have a problem with ADO. However, you said "For some reason, using ADO to pull records from Access into Excel was causing problems when Nulls were present" in your article. I am not sure what you are referring to.

        • Sorry, I thought you were asking. I don't know what the problem is. When I assign the results of an ADO recordset to a VBA array and try to iterate it, I get an error message (something like "Nulls not allowed"). To solve that I simply remove all Null values from the database.

  2. The code you provided does not loop through all the records in the table, so it works only on the first record in each table.

    • Let's say I have a table called "People" with two text fields, "First_Name" and "Last_Name". This procedure would execute the following SQL statements on the table:

      [cc lang='sql']UPDATE [People] SET [First_Name] = "" WHERE [First_Name] Is Null;
      UPDATE [People] SET [Last_Name] = "" WHERE [Last_Name] Is Null;[/vb]

      These statements act on every Null record in the field, converting them to empty strings. I'm hard pressed to understand why you would need to loop through every single record individually when the SQL does it for you.

  3. I found my way around to 'trim all fields' by Query.

    I'll explain how if you reply that you still need it.

This article is closed to any future comments.
Random Data Generator