code to search for nulls

[Guest]

I have inheritted a db that needs to be checked for nulls and reported on.
I'm trying to automate this process. The previous method was to run a query
export as Excel and search the block of data for errors manually. Using the
existing query, i want to take any null values (designated by an "X") and put
them in a sperate table. I will then Export that table to Excel and send the
null values report to the appropriate people. I have written code to search
each field in the query for an "X". Is there a way to code this so I don't
have to write out each field? There are 270 elements that need to be
searched. Here is my code thus far. It will work fine for one element then
give me a "Record Not Found" Error on the next field.

Option Explicit
Public intCathID As Integer
Public strFieldDef As String
Public intFieldCount As Integer
Private Sub ErrorCheckA()


Dim rst As DAO.Recordset
Dim rstError As DAO.Recordset
Dim db As DAO.Database
Dim strTest As String

'set counter to 0
intFieldCount = 20

Set db = CurrentDb()
Set rst = db.OpenRecordset("30DefTest")
Set rstError = db.OpenRecordset("tblError")

'Delete existing entries in error table
With rstError

Do Until .EOF
rstError.Delete
rstError.MoveNext

Loop

End With

With rst


'Do Until .EOF

Do While intFieldCount < 44

strTest = rst(intFieldCount)

If rst(intFieldCount) = "X" Then

intCathID = rst!SS_Event_Cath_ID
strFieldDef = rst(intFieldCount)
Call UpdateError
'Else

End If
rst.MoveNext

If .EOF Then
intFieldCount = intFieldCount + 1
'Call UpdateError
'Else
'Call UpdateError

End If
Loop

End With
rst.Close
rstError.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblerror",
"C:\Documents and Settings\All Users\Desktop\CathErrors"

'DoCmd.SendObject acSendTable, "tblError", acFormatXLS, "(e-mail address removed)", , ,
"Test", "Testing this code. The cool part is this is all done with one
button push.", True

End Sub
'This routine updates the error table
Private Sub UpdateError()
Dim rst As DAO.Recordset
Dim rstError As DAO.Recordset
Dim db As DAO.Database
Dim strWhere As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("30DefTest")
Set rstError = db.OpenRecordset("tblError")

strWhere = "SS_Event_Cath_ID = " & intCathID

With rst
rst.FindFirst strWhere

rstError.AddNew
If rst(intFieldCount) = "X" Then
rstError!EventCathID = rst!SS_Event_Cath_ID
rstError!MRN = rst!Patient_ID
rstError!CathDate = rst!Date_of_cath
rstError!PatLast = rst!Last_Name
rstError!Fellow = rst!Cath_Fellow
rstError!attending = rst!Cath_Attending
rstError!Error = "Missing"
'rstError!Field = strFieldDef
rstError!Field = rst(intFieldCount).Name
rstError.Update
End If
End With

End Sub

 

[Wayne Morgan]

Are you needing to find a Null in any field or just certain fields? Can you
set the table to not allow nulls in that field so that the problem doesn't
occur?

There may be some things you can do with queries. For example, the code

'Delete existing entries in error table
With rstError
Do Until .EOF
rstError.Delete
rstError.MoveNext
Loop

could be shortened to

CurrentDb.Execute "DELETE * FROM tblError;", dbFailOnError

You may also be able to open the recordsets from queries that only return
records with Null values in the field(s).

 

[Guest]

Unfortunately, I can't set the table(s) to disallow nulls. There are 270
specifc fields i am looking for nulls. They go in order in the query
"30DefTest". That's why I was using the counter.

 

[Wayne Morgan]

Try creating a query that uses all fields in the table. For the criteria,
use

WHERE [Field1] Is Null Or [Field2] Is Null Or [Field3] Is Null

Add all the fields to the string above. When you open this query or use it
to create a recordset, only the records with a Null in one or more fields
will be returned. If you want to export to Excel, you could export the query
to Excel without opening a recordset first.