Reporting on nulls within a Database

[Chris]

Hi Group,

I had posted my original question (copied below) to the
Access Queries Group but it has been suggested that it
would be better to post it to this Group. The suggestion
was provided by Ted Allen from Microsoft who also provided
some analysis into solving the problem. In response to
his analysis I have provided necessary info to address the
questions he raised. I do not have a lot of programming
experience with VBA but I have used it before.

If anyone can help me with compling, by applicantID, a
list of fields that contain nulls I would be very
grateful - it could save me loads of work and time.

Best regards

Chris


********* Question update *********

The Database contains required details of applicants
(applicantID) who apply for grants to start up
businesses. There are many conditions that must be met
before funding is granted, i.e. Location, income, budget,
etc. Not all tables are linked to the primary table
(tblApplication) but linked tables do share field name
ApplicationID. (Quite a few of the other tables are look-
ups and are not required to be analysed). All tables are
linked by 1:many and I would like to log all instances of
nulls. I would also like to treat a zero length string as
a null as well.


********* Ted Allen's Response posted 10/01/05 *********

Hi Chris,

It seems like your best bet may be to use VB code to loop
through the tables
and fields values and log all of the field names with null
values a table
with the associated ApplicationID's. But, are you sure
all 100 tables link
to the primary table? And, if so, do all use the same
field name
"ApplicationID"? If they do, you should be able to write
code to loop
through all ApplicationID's. If they don't, you still may
be able to - but
it will likely require some extra thinking and programming
steps.

You may want to post in the modulesdaovba group with a
little more detail on
whether you have any programming experience, whether all
linked tables use
the same field name, whether you want to treat a zero
length string as a
null, whether linked tables are 1:1, 1:many (if 1:many, do
you want to log
all instances of nulls, or ignore as long as one record
has a non-null
value), etc, so that you can get help with the code.

HTH, Ted Allen

********* Original Question - posted 10/01/05 *********

Hi Group.

I have a very big job ahead of me and I would appreciate
some advice before I begin.
I have inherited an applications Database in work, which
contains about 100 tables and approx 5000 records.
The master table in my Database is called tblApplication
and each table is linked by primary key field
ApplicationID.
In order to clean up the Database to allow for better
reporting Management have asked me to produce, by
applicant (ApplicationID), a list of fields that contain
Nulls, like so...

ApplicationID: 123456
Address1 (Table1) }
Approved (Table2) } Illustration only
Amount (Table3) }

The idea being that once the blank fields have been
identified staff will be asked to update these fields with
required data (data that should have been added in the
first place).

Does anyone know how I could go about this - I can produce
a Nulls query for a single field but how do I expand this
to include all fields and tables within the Database?

Regards

Chris

 

[Andi Mayer]

I have a very big job ahead of me and I would appreciate
some advice before I begin.
I have inherited an applications Database in work, which
contains about 100 tables and approx 5000 records.
The master table in my Database is called tblApplication
and each table is linked by primary key field
ApplicationID.
In order to clean up the Database to allow for better
reporting Management have asked me to produce, by
applicant (ApplicationID), a list of fields that contain
Nulls, like so...

ApplicationID: 123456
Address1 (Table1) }
Approved (Table2) } Illustration only
Amount (Table3) }

The idea being that once the blank fields have been
identified staff will be asked to update these fields with
required data (data that should have been added in the
first place).

Does anyone know how I could go about this - I can produce
a Nulls query for a single field but how do I expand this
to include all fields and tables within the Database?

Regards

Chris

Dim db as DAO.Database
dim tbl as DAO.tabledef
Dim fld as DAO.field
dim rs as DAO.recordset

set db=currentdb
for each tbl in db.tabledefs
set rs="SELECT * FROM " &tbl.name
while not rs.EOF
for each fld rs.fields
if fld="" or isnull(fld) then
' add the field, TableName,....
' to a table
endif
next fld
rs.movenext
wend
rs.close
next tbl

set fld =nothing
set tbl =nothing
set rs =nothing
set db =nothing

now use the table to show the user which fields has to be filled in

 

[Chris]

Andi,

Many thanks for your quick response.
Where do I paste the code to get it working? I tried
adding it behind a form but it threw up a message saying
type mismatch error. Am I doing something wrong?

Also, where you have the lines...

' add the field, TableName,....
' to a table

do I need to add code to get fieldnames added to a table
(tblNulls)?

Much appreciated

Tony

-----Original Message-----


Dim db as DAO.Database
dim tbl as DAO.tabledef
Dim fld as DAO.field
dim rs as DAO.recordset

set db=currentdb
for each tbl in db.tabledefs
set rs="SELECT * FROM " &tbl.name
while not rs.EOF
for each fld rs.fields
if fld="" or isnull(fld) then
' add the field, TableName,....
' to a table
endif
next fld
rs.movenext
wend
rs.close
next tbl

set fld =nothing
set tbl =nothing
set rs =nothing
set db =nothing

now use the table to show the user which fields has to be filled in

word "manfred" to the first 10 lines in the message

 

[Chris]

Andi,

Many thanks for your quick response.
Where do I paste the code to get it working? I tried
adding it behind a form but it threw up a message saying
type mismatch error. Am I doing something wrong?

Also, where you have the lines...

' add the field, TableName,....
' to a table

do I need to add code to get fieldnames added to a table
(tblNulls)?

Much appreciated

Tony

-----Original Message-----


Dim db as DAO.Database
dim tbl as DAO.tabledef
Dim fld as DAO.field
dim rs as DAO.recordset

set db=currentdb
for each tbl in db.tabledefs
set rs="SELECT * FROM " &tbl.name
while not rs.EOF
for each fld rs.fields
if fld="" or isnull(fld) then
' add the field, TableName,....
' to a table
endif
next fld
rs.movenext
wend
rs.close
next tbl

set fld =nothing
set tbl =nothing
set rs =nothing
set db =nothing

now use the table to show the user which fields has to be filled in

word "manfred" to the first 10 lines in the message

 

[Chris]

Andi,

Many thanks for your quick response.
Where do I paste the code to get it working? I tried
adding it behind a form but it threw up a message saying
type mismatch error. Am I doing something wrong?

Also, where you have the lines...

' add the field, TableName,....
' to a table

do I need to add code to get fieldnames added to a table
(tblNulls)?

Much appreciated

Tony

-----Original Message-----


Dim db as DAO.Database
dim tbl as DAO.tabledef
Dim fld as DAO.field
dim rs as DAO.recordset

set db=currentdb
for each tbl in db.tabledefs
set rs="SELECT * FROM " &tbl.name
while not rs.EOF
for each fld rs.fields
if fld="" or isnull(fld) then
' add the field, TableName,....
' to a table
endif
next fld
rs.movenext
wend
rs.close
next tbl

set fld =nothing
set tbl =nothing
set rs =nothing
set db =nothing

now use the table to show the user which fields has to be filled in

word "manfred" to the first 10 lines in the message

 

[Andi Mayer]

Andi,

Many thanks for your quick response.
Where do I paste the code to get it working? I tried
adding it behind a form but it threw up a message saying
type mismatch error. Am I doing something wrong?

this was untested code, written into the news reader, therefore
i forgot the "in" in this line (this line is red!!!)
For Each fld In rs.Fields

Also, where you have the lines...

' add the field, TableName,....
' to a table

do I need to add code to get fieldnames added to a table
(tblNulls)?

this produces a table:myEmptyFields
where you find after running this sub:
tName: the table Name
fName: the field name
ID: if the checked table has a field ApplicationID

paste this into a module:
and check the references for DAO

Sub test()
Dim TableName As String
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
TableName = "myEmptyFields"
Set db = CurrentDb
On Error Resume Next
Set tbl = db.TableDefs(TableName)
If tbl Is Nothing Then
On Error Resume Next
db.Execute "CREATE TABLE " & TableName _
& " (tName TEXT, fName TEXT, ID TEXT);"
db.TableDefs.Refresh
else
set tbl=nothing
db.execute "DELETE * FROM " & TableName
End If

On Error GoTo 0
Set rs1 = db.OpenRecordset("SELECT * FROM " _
& TableName, , dbAppendOnly)
For Each tbl In db.TableDefs
If tbl.Name <> "myEmptyFields" _
And Left(tbl.Name, 1) <> "~" _
And Left(tbl.Name, 4) <> "MSYS" Then
Set rs = db.OpenRecordset("SELECT * FROM " & tbl.Name)
While Not rs.EOF
For Each fld In rs.Fields
If fld = "" Or IsNull(fld) Then
rs1.AddNew
rs1!tName = tbl.Name
rs1!fName = fld.Name
If fld.Name = "ApplicationID" Then
rs1!ApplicationID = rs!ApplicationID
End If
rs1.Update
End If
Next fld
rs.MoveNext
Wend
rs.Close
End If
Next tbl
rs1.Close
Set fld = Nothing
Set tbl = Nothing
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing
End Sub

 

[Chris]

Andi,

Thank you once again for your quick response. Almost
there...

I've pasted the code into a module, checked for DAO and
hit F5. The code returns an error... Run-time error '13':
Type mismatch and then highlights the following line...

If fld = "" Or IsNull(fld) Then

Although the code sucessfully creates the table
myEmptyFields no data is added. Any ideas?

Cheers

Chris

-----Original Message-----


this was untested code, written into the news reader, therefore
i forgot the "in" in this line (this line is red!!!)
For Each fld In rs.Fields


this produces a table:myEmptyFields
where you find after running this sub:
tName: the table Name
fName: the field name
ID: if the checked table has a field ApplicationID

paste this into a module:
and check the references for DAO

Sub test()
Dim TableName As String
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
TableName = "myEmptyFields"
Set db = CurrentDb
On Error Resume Next
Set tbl = db.TableDefs(TableName)
If tbl Is Nothing Then
On Error Resume Next
db.Execute "CREATE TABLE " & TableName _
& " (tName TEXT, fName TEXT, ID TEXT);"
db.TableDefs.Refresh
else
set tbl=nothing
db.execute "DELETE * FROM " & TableName
End If

On Error GoTo 0
Set rs1 = db.OpenRecordset("SELECT * FROM " _
& TableName, , dbAppendOnly)
For Each tbl In db.TableDefs
If tbl.Name <> "myEmptyFields" _
And Left(tbl.Name, 1) <> "~" _
And Left(tbl.Name, 4) <> "MSYS" Then
Set rs = db.OpenRecordset("SELECT * FROM " & tbl.Name)
While Not rs.EOF
For Each fld In rs.Fields
If fld = "" Or IsNull(fld) Then
rs1.AddNew
rs1!tName = tbl.Name
rs1!fName = fld.Name
If fld.Name = "ApplicationID" Then
rs1!ApplicationID = rs!ApplicationID
End If
rs1.Update
End If
Next fld
rs.MoveNext
Wend
rs.Close
End If
Next tbl
rs1.Close
Set fld = Nothing
Set tbl = Nothing
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing
End Sub

word "manfred" to the first 10 lines in the message

 

[Andi Mayer]

Andi,

Thank you once again for your quick response. Almost
there...

I've pasted the code into a module, checked for DAO and
hit F5. The code returns an error... Run-time error '13':
Type mismatch and then highlights the following line...

If fld = "" Or IsNull(fld) Then

change it to
If Nz(fld, "") = "" Then

if you get an error again type into the immediate window:

?fld.Name, fld.Type , "|" &fld.Value &"|"

and send the result

BTW: this is giving you also empty fields

if you only wants fields with never inserted Data then you have to
use:
if isnull(fld) then

 

[Chris]

Hi Andi,

I appreciate your patience and expertise.
I added new suggestions.

New code looks like this..

For Each fld In rs.Fields
'If fld = "" Or IsNull(fld) Then
If Nz(fld, "") = "" Then

New error returned Run-time error 3265 'Item not found in
this collection'.

Here is the result from the immediate window..

ApplicationID 4 ||

The code seems to be almost working as it adds nulls to
tName and fName only.

Thinking out aloud here but does the above code work with
queries? I was thinking that if I could build a query,
add the required tables and fields, could it amended to
analyse that query only to produce the same results - that
way I can target the necessary tables and fields?

Thanks again

Tony

-----Original Message-----

change it to
If Nz(fld, "") = "" Then

if you get an error again type into the immediate window:

?fld.Name, fld.Type , "|" &fld.Value &"|"

and send the result

BTW: this is giving you also empty fields

if you only wants fields with never inserted Data then you have to
use:
if isnull(fld) then

word "manfred" to the first 10 lines in the message

 

[Guest]

Hi Chris, Andi,

I was looking over the earlier posts and I am wondering if the error was
being generated when numeric fields were being compared to the empty string
(different data types and thus type mismatch). Maybe try the following:

If CStr(Nz(fld, "")) = "" Then

Or, if that doesn't work, try just running for nulls first to see if that
will work (then we could think more about how to handle zero length strings).
In that case, you could try:

If IsNull(fld) Then

HTH, Ted Allen

 

[Andi Mayer]

On Tue, 11 Jan 2005 07:59:09 -0800, Ted Allen

Ted:
no I checked it with a bunch of copied table's

it's the ApplicationID field

Chris:

goto to the Table and set the field ApplicationID
AllowNullValues to true

Thinking out aloud here but does the above code work with
queries? I was thinking that if I could build a query,
add the required tables and fields, could it amended to
analyse that query only to produce the same results - that
way I can target the necessary tables and fields?

I don't understand that

this table gives you the list off all fields in all tables in your
database without knowing the structure of your database.

now you can filter this table for the fields you want
and use this result then for editing your database

 

[Chris]

Andi,

This could prove to be a problem :-(
The db is stored on a SQL server, and I am not allowed to
access it. Even if I was, I would not be allowed to
change the db tables without prior permission from the
designers (no chance - would take months to authorise!)

I am writing this message from my home account but I will
check to see if your analysis is correct when I get back
to the office tomorrow.

In the meantime and I hope you don't mind, and just to
cover myself (I have to have this report ready by tomorrow
at 5pm - gulp!), I've had to make another post to the
queries Group to try a different approach.

Thanks for all your help and expertise so far, it has been
a useful learning experience. Will let you know how I get
on.

Cheers

Chris

-----Original Message-----
On Tue, 11 Jan 2005 07:59:09 -0800, Ted Allen

Ted:
no I checked it with a bunch of copied table's

it's the ApplicationID field

Chris:

goto to the Table and set the field ApplicationID
AllowNullValues to true



I don't understand that

this table gives you the list off all fields in all tables in your
database without knowing the structure of your database.

now you can filter this table for the fields you want
and use this result then for editing your database

word "manfred" to the first 10 lines in the message

 

[Andi Mayer]

Andi,

This could prove to be a problem :-(
The db is stored on a SQL server, and I am not allowed to
access it. Even if I was, I would not be allowed to
change the db tables without prior permission from the
designers (no chance - would take months to authorise!)

no no no

in access: the table I created : myEmptyFields
then my tables allows you to insert null-Values (standard is no_null)
I forgot it in the table-create statement

 

[Chris]

Hi Andi,

I changed the ApplicationID field in table myEmptyFields
as requested to allows nulls - when you refer to
AllowNullValues I assume you mean Allow Zero String?

The code generated an error 'item does not exist in this
collection'. So I changed

If fld.Name = "ApplicationID " Then
rs1!ApplicationID = rs!ApplicationID
to..

If fld.Name = "ApplicationID " Then
rs1!ID= rs!ApplicationID

to match the same field named created in the create table
statement.

I ran the code again and it generated a type mismatch
error and highlighted the following line.

If Nz(fld, "") = "" Then


Any ideas now?

Chris

-----Original Message-----

no no no

in access: the table I created : myEmptyFields

then my tables allows you to insert null-Values (standard is no_null)
I forgot it in the table-create statement

word "manfred" to the first 10 lines in the message

 

[Andi Mayer]

Hi Andi,


If fld.Name = "ApplicationID " Then
rs1!ID= rs!ApplicationID

right, this was an error (sorry)

I ran the code again and it generated a type mismatch
error and highlighted the following line.

If Nz(fld, "") = "" Then

the last time
?fld.Name, fld.Type , "|" &fld.Value &"|" gave
ApplicationID 4 ||

that means: AppplicationID is a long and the Value is Null

this should give an error

check your references for the word MISSING (Tools references)

and try with:
If isnull(fld) then

How Big is the mdb if zipped?