Null Values

[dcarr]

Hi, is there anyway you can create a query to search a whole table for null values in any column? I

 

[Lister]

In the criteria of the feild that you want to bring the null values.
Enter "Is Null"

See how you get on

 

[dcarr]

sorry. I just wanted to do a generci search of the table for null values. I am aware of the is null but I have 75 fields with data containing around 1,300,000 records. Just wanted to search the whole table for null values.
Thanks

 

[namliam]

    Dim I As Integer
    For I = 0 To CurrentDb.TableDefs("YourTable").Fields.Count - 1
        Debug.Print CurrentDb.TableDefs("YourTable").Fields(I).name
    Next I

Will display all fields in the given table ....

you can addapt this to build a query where field1 is null or field2 is null etc...

Regards

The Mailman

 

[dcx693]

It's actually just slightly more complicated than namliam wrote. While his code example will let you loop through the table structure, it won't actually let you get at the data.

You need to open up the table as a recordset. If you're using Access 97, here's some sample code:

Sub FindNulls()
Dim rst As DAO.Recordset
Dim intI As Integer

    Set rst = CurrentDb.OpenRecordset("your_table_name")
    
    If Not rst.BOF And Not rst.EOF Then
        While Not rst.EOF
            For intI = 0 To rst.Fields.Count - 1
                If IsNull(rst.Fields(intI)) Then
                    Debug.Print rst.Fields(intI).Name
                End If
            Next intI
            rst.MoveNext
        Wend
    End If
    
    rst.Close
    Set rst = Nothing
    
End Sub

 

[namliam]

    Dim I As Integer
    For I = 0 To CurrentDb.TableDefs("YourTable").Fields.Count - 1
        Debug.Print CurrentDb.TableDefs("YourTable").Fields(I).name
    Next I

Will display all fields in the given table ....

you can addapt this to build a query where field1 is null or field2 is null etc...

Regards

The Mailman

dcx as noted:
Will display all fields in the given table ....

you can addapt this to build a query where field1 is null or field2 is null etc...


Regards

 

[namliam]

ALSO

Snip from your code:
Set rst = CurrentDb.OpenRecordset("your_table_name")
Debug.Print rst.Fields(intI).Name

Note that if i take out the rst it looks like this:
Debug.Print CurrentDb.OpenRecordset("your_table_name")
.Fields(intI).Name

which is freakishly simular to this (taken from my code)
Debug.Print CurrentDb.TableDefs("YourTable").Fields(I).name

Except you have to replace your openrecordset by tabledefs since you wont be using a recordset but the table directly.

Furthermore your code will run ALL records directly whereas my suggestion was to addapt my code to build some SQL so you can query the table for the records containing the null value...

Regards