Replacing ALL Null values in a Table

[jhlee2222]

I've searched the forum a good bit (as well as exercised a bit of Google Fu for the Internet in general) and I can't find an answer to what would appear to be a simple task.

I'm attempting to write a query to replace any and all field values that are null in a table with the string "NULL". In searching, I've found numerous methods to accomplish this. However, every method involves specifying the name of the table field in the query and running the query for each field. I'm trying to make a query which could replace all null values on a table, regardless of how many fields the table may have or what the field names are.

From what I can tell, this isn't possible with a standard query, since "*" can't be used to specify all fields in a table. Maybe in combination with some macro code to read in the values of every column and run the query on it?



I'm really looking for the simplest method, but I'm afraid something simple just isn't going to cut it.

[lfpm062010]

It is a time consuming task. Do this one field at a time.
Try this,

UPDATE TABLE1 SET TABLE1.FIELD1 = "NULL" WHERE TABLE1.FIELD1 IS NULL;

The other way is VB.

Loop through the recordset and use IF statement and set "NULL" when found the field IS NULL.

[June7]

Why do you want to do this?

Can simply use Nz in queries or textbox expression to handle the Null

SELECT Nz([fieldname],"NULL") As FieldNameAdj FROM tablename;

If you must UPDATE, try:

UPDATE tablename SET field1=Nz(field1,"NULL"), field2=Nz(field2,"NULL");

[jhlee2222]

June7 and lfpm062010, thank you both for the quick responses on this.

Your answers though illustrate why what I'm looking for is a bit unusual, or outside of what I've seen in searching this and other formums. I'm attempting to come up with a query that would replace the nulls found in any field of ANY table it's pointed at, hence solutions that reference specific field names won't really accomplish what I'm trying to do.

I'm working on some projects with in-house tools that do not do well with null values. Our workaround when creating tables to export to these tools has been to manually replace null values or run queries on each field, turning null values to "NULL". I'm hoping to create a query or macro to do this with one click to any table, regardless of the names of the fields.

I really don't want to go the macro route if possible, but I'm guessing writing a macro to feed in the name of each field of the target table to an array, then run the same update query on each field in the array, may be the only path to what I want to accomplish.

Please prove me wrong.

[ItsMe]

I believe you will need to enumerate all fields in all tables. Consider the fact that all data types that allow nulls will not allow text. For instance, a date field may be null. Have you ever tried to enter the literal text "Null" in a date field? Yes No fields can cause conflicts when you try to assign literal text values.

Having said that, the data types you are dealing with may not have these conflicts since you are getting these tables from elsewhere. Just consider the data types of the fields before you assign a value to it.

This thread seems to have some code examples to get you started.
http://www.access-programmers.co.uk/...ad.php?t=99194

[June7]

That will require VBA (not macro). ItsMe pretty much covers primary considerations - number field types cannot have string value. Yes/No and Date are numeric types.

I am not sure how code in the referenced link can be applied to your situation.

Code can open a recordset of a table and cycle through the fields by referencing the field index, like:

Sub test1()
strTable = InputBox("Enter table name")
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [" & strTable & "]")
While Not rs.EOF
rs.Edit
For i = 0 To rs.Fields.Count - 1
rs.Fields(i) = Nz(rs.Fields(i), "NULL")
Next
rs.Update
rs.MoveNext
Wend
End Sub

[ItsMe]

...I am not sure how code in the referenced link can be applied to your situation...

I was thinking of using some of the code in the link to determine the field types. With that, the OP can decide how to update the field. This code below incorporates some of the link's code with June's example.

strTable = InputBox("Enter table name")
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [" & strTable & "]")
    While Not rs.EOF
        rs.Edit
            For i = 0 To rs.Fields.Count - 1
                Select Case rs.Fields(i).Type
                Case 10, 12 'Will select only text, hyperlink, and memo field types
                    rs.Fields(i) = Nz(rs.Fields(i), "NULL")
                End Select
            Next
        rs.Update
        rs.MoveNext
    Wend

This code is not taking into account indexed fields that do not allow duplicates. Further enumeration would have to be implemented to manage other data types and or indexed fields. I believe the only way to manage this is with table defs and field properties. So there may be limitations with this example that are not acceptable to the OP's circumstances.

[June7]

Assuming indexed fields that do not allow duplicates will therefore never be null, use of the Nz() expression might be adequate.

[ItsMe]

When I was testing the code in post #7 I tried to expand the Select Case block of code by including long and integer data types. By doing so, the Autonumber field caused the code to create an exception. The debugger did not identify duplicate key values as an issue. However, using tabledefs I was able to retrieve the name of the Primary Key field and bypass updating this field. Doing so allowed the code to update the remaining long integer fields to 0.
After considering the duplicate nulls in an indexed field that does not allow duplicate values, i decided to test it. In Access 2010 I created an indexed text field that does not allow duplicates. I created multiple records in this new test table, leaving the indexed text field alone and adding values to another field.
When I ran the code in post #7 it errored with 3022. I then added an error trap to handle the exception. The following code works for text fields that do not allow duplicates. THe drawback is that it will edit/update the first record that is null to "Null". I suppose you would have to go back and update the orphans.

On Error GoTo Err_Find_Type
strTable = InputBox("Enter table name")
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [" & strTable & "]")
    While Not rs.EOF
        rs.Edit
            For i = 0 To rs.Fields.Count - 1
                Select Case rs.Fields(i).Type
                Case 10, 12 'Will select only text, hyperlink, and memo field types
                    rs.Fields(i) = Nz(rs.Fields(i), "NULL")
                End Select
            Next
        rs.Update
        rs.MoveNext
    Wend
Exit Sub
Err_Find_Type:
    If Err.Number = 3022 Then
        Resume Next
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If

[ItsMe]

I believe the best solution is to enumerate the fields and store the info in a temp table. THis will allow you to have a reference while you run your update queries. The following will allow you to iterate through a given table and store the info you will later need.

With the table populated, you will then be armed with the necessary information to update the tables.

in order for the code to work you need a temp table with the following....

FirstPK (AutoNumber)
FieldPosition (Number)
TableName (Text)
FieldName (Text)
IsUnique (Yes/No)
FieldType (Number)

'Create some objects to update the definaitions table
Dim rsCreate As DAO.Recordset
Set rsCreate = CurrentDb.OpenRecordset("tblDefs", dbOpenDynaset)
'Need to declare some objects to enumerate the fields
Dim tdfFind As TableDef
Dim idxFind As Index
Dim fldFind As Field
'Create a couple variables
Dim intField As Integer
Dim strFieldName As String
Dim strTable As String
strFieldName = ""
strTable = "tblEquipTestNull"
    For Each tdfFind In CurrentDb.TableDefs
    
        If tdfFind.Name = strTable Then
        
            intField = 0
        
            For Each fldFind In tdfFind.Fields  'Find all of the field names in the current table
            
                With rsCreate
                    .AddNew
                    ![FieldPosition] = intField
                    ![TableName] = strTable
                    ![FieldName] = fldFind.Name
                    ![FieldType] = fldFind.Type
                    .Update
                End With
            intField = intField + 1
            Next fldFind
        
                'Check out the indexed fields and look for unique properties
                For Each idxFind In tdfFind.Indexes
                
                strFieldName = Mid(idxFind.Fields, 2)   'Drill down to the fields collection and get the name from there
                
                    With rsCreate
                    'Each of these fields is an indexed field. We will define the ones that do not allow duplicates in our temp table
                    .FindFirst "[TableName] = '" & strTable & "'" & " AND [FieldName] = '" & strFieldName & "'"
                    
                        If Not .NoMatch Then
                            .Edit
                            ![IsUnique] = idxFind.Unique    'distinguishes if duplicates are allowed
                            .Update
                        Else
                        Debug.Print "NoMatch for " & strFieldName   'Should not be any names printed.
                        End If
                    
                    End With
                
                Next idxFind
        
        End If '= strTable
    
    Next tdfFind
rsCreate.Close
Set rsCreate = Nothing

[jhlee2222]

ItsMe, Thank you for the very detailed code. If I understand it correctly, this code will only feed in the names of the table fields, right? How would you go about using the field names in a query from that point, looping the query to execute for each field in your temp table?

[ItsMe]

I would have to look and see what the heck I wrote and then consider your question. Give me a little bit to see what I can come up with.

[ItsMe]

You would need to compare your results for each field you find in your rs. So a second DAO recordset should be created. Here I use the FindFirst method using the field's index. With that we can compare the values in the table. You could probably expand on this by not soft typing the table names. Maybe loop through each table when you are doing the field enumerations and then call this code in a function, passing the table name to the UDF. Anyway....

On Error GoTo Err_Find_Type
strTable = InputBox("Enter table name")
Dim intType As Integer
intType = 0
Dim rsFindField As DAO.Recordset
Set rsFindField = CurrentDb.OpenRecordset("SELECT * FROM tblDefs " & _
                                          "WHERE([tblDefs].[TableName]) = '" & strTable & "'", dbOpenDynaset)
    If rsFindField.EOF Then
        MsgBox "You did not populate your definitions table! Request aborted."
        rsFindField.Close
        Set rsFindField = Nothing
    Exit Sub
    End If
    
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [" & strTable & "]")
    While Not rs.EOF
        rs.Edit
            For i = 0 To rs.Fields.Count - 1
            
            rsFindField.FindFirst "[FieldPosition] = " & i
            If Not IsNull(rsFindField![FieldType]) Then
            intType = rsFindField![FieldType]
            End If
            
                Select Case intType
                Case 10, 12 'Will select only text, hyperlink, and memo field types
                    If rsFindField![IsUnique] = 0 Then    'Not Unique Index and can be updated
                        rs.Fields(i) = Nz(rs.Fields(i), "NULL")
                    End If
                    
                Case 3, 4 'Will select only integer and long integer types
                    If rsFindField![IsUnique] = 0 Then    'Not Unique Index and can be updated
                    rs.Fields(i) = Nz(rs.Fields(i), 0)
                    End If
                'You can add other clauses for additional Data types like Yes/No
                End Select
            Next
        rs.Update
        rs.MoveNext
    Wend
rsFindField.Close
Set rsFindField = Nothing
rs.Close
Set rs = Nothing

    
Exit_My_Error:
Exit Sub
Err_Find_Type:
    If Err.Number = 3022 Then
        Resume Next
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
Resume Exit_My_Error

[jhlee2222]

ItsMe,

Again, thank you. This is some complicated code for my level of understanding. I did get it to work, to a point.

I had to separate the code you listed on 12/28 at 10:48PM from the code you listed most recently as different procedures.

I created a new table "tblDefs" per your instructions. The first procedure runs and updates that table with my target table's details.

I then attempt to run the latest code, entering the name of the target table. However, I'm getting a "3164: Field cannot be updated" error.

Any ideas what I'm doing wrong?

[ItsMe]

That is the idea to run the code in post #10 first. This will populate the tblDefs with the data needed for the code in post #13. I mentioned there could be the possibility to combine the two but, if you are having troubles understanding the flow of things just do it separately. One time to get the field properties into tblDefs and the second time (using the code from post #13) to update the original tables.

As for why you are getting the error, I can not imagine why. I would like to think that I took into consideration the possibilities of running into write conflicts. One thing could be referential integrity rules. Can't imagine how the rule would conflict but it is something I did not test. Also, I added some code in Post # 13 to update number types to 0. Comment this code out if this is something you do not want. I included it to provide an example for data types that are not Text.

So, give this a try; import one of the tables to a blank DB. Maybe the one that is giving an error. Build a form with a couple of control buttons for the two separate procedures I provided. Copy a blank or build a new tblDefs. Execute the code and if it still gives you errors delete all of the records from the tables and upload the DB to this forum here. When I get a chance I will take a look.

Maybe, in the interest of time, if you still get the errors, test the DB one last time before uploading after you delete the records. Just add a couple records back into the table by placing some dummy info in one of the fields to do your last test before uploading.