Blank Fields Report

  • Thread starter Thread starter nl
  • Start date Start date
N

nl

How can I create a report that will show only the field names that were left
blank on a record?

thank you.

NL
 
Have you tried to run a query with the criteria of IS NULL on each of the
fields you wish to check?
 
Yes, I did. but what I want it's a report, if there are 20 records, to show
only the field names that are blank for each record
 
Can you use the query as your record source and place an unbound text box on
the report for each field in the table. For each textbox have something like
this =iif(isnull([fieldname],"NULL","HAS DATA")). (or true/false, yes/no...
whatever. Your column headings can be the names of the fields, in the detail
section you will see whether or not the field has data or not. This may not
be very pretty, but it might give you the information you are looking for.
 
Maybe I'm not explaining myself well.
What I want is a report that would tell me the records that some fields are
missing information, only specifying the fields that are missing information.
Example. Record1, field State is missing, (something like that).
Record2, field zipcod is missing........
SuzyQ said:
Can you use the query as your record source and place an unbound text box on
the report for each field in the table. For each textbox have something like
this =iif(isnull([fieldname],"NULL","HAS DATA")). (or true/false, yes/no...
whatever. Your column headings can be the names of the fields, in the detail
section you will see whether or not the field has data or not. This may not
be very pretty, but it might give you the information you are looking for.

nl said:
Yes, I did. but what I want it's a report, if there are 20 records, to show
only the field names that are blank for each record
Click to expand...
Click to expand...
 
You could do something like this, although it would require frequent
compacting of database...But this might give you an idea.

Create a table, call it blankFields with RecordNumber (long) and FieldList
(text) as fields. Create a report and set the data source to that table. In
the on close and on open events put the following (air code)

The on Open event fills the blankFields table, the close event deletes the
records that it added.

Dim dteFreezeTime As Date

Private Sub Report_Close()

'delete the records added during this run of report
Dim strSQL As String

strSQL = "DELETE * "
strSQL = strSQL & "FROM [blankFields ] "
strSQL = strSQL & "WHERE [freezeTime] = #" & dteFreezeTime & "#"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

Private Sub Report_Open(Cancel As Integer)

Dim strSQL As String
Dim lngRecNo As Long
Dim strFields As String

Dim rs As DAO.Recordset

dteFreezeTime = Now()

strSQL = "SELECT TableName.F1, TableName.f2, TableName.f3, TableName.f4,
TableName.pk "
strSQL = strSQL & "FROM TableName "
strSQL = strSQL & "Where (TableName.F1 Is Null) OR (TableName.f2 Is
Null) OR (TableName.f3 Is Null) OR (TableName.f4 Is Null) "
strSQL = strSQL & "ORDER BY TableName.pk"

Set rs = CurrentDb.OpenRecordset(strSQL)

'initialize vars
strFields = "Fields: "

Do While Not rs.EOF
lngRecNo = rs!pk

If IsNull(rs!f1) Then
strFields = strFields & "F1"
End If

If IsNull(rs!f2) Then
If Len(strFields) = 7 Then
strFields = strFields & "F2"
Else
strFields = strFields & ", F2"
End If
End If
If IsNull(rs!f3) Then
If Len(strFields) = 7 Then
strFields = strFields & "F3"
Else
strFields = strFields & ", F3"
End If
End If
If IsNull(rs!f4) Then
If Len(strFields) = 7 Then
strFields = strFields & "F4"
Else
strFields = strFields & ", F4"
End If
End If

strSQL = "INSERT INTO blankFields (RecordNumber, FieldList,
freezeTime) "
strSQL = strSQL & "VALUES (" & lngRecNo & ", '" & strFields & "', #"
& dteFreezeTime & "#) "

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)

rs.MoveNext
Loop

Me.Filter = "[freezeTime] = #" & dteFreezeTime & "#"
Me.FilterOn = True

End Sub








nl said:
Maybe I'm not explaining myself well.
What I want is a report that would tell me the records that some fields are
missing information, only specifying the fields that are missing information.
Example. Record1, field State is missing, (something like that).
Record2, field zipcod is missing........
SuzyQ said:
Can you use the query as your record source and place an unbound text box on
the report for each field in the table. For each textbox have something like
this =iif(isnull([fieldname],"NULL","HAS DATA")). (or true/false, yes/no...
whatever. Your column headings can be the names of the fields, in the detail
section you will see whether or not the field has data or not. This may not
be very pretty, but it might give you the information you are looking for.

nl said:
Yes, I did. but what I want it's a report, if there are 20 records, to show
only the field names that are blank for each record
:

Have you tried to run a query with the criteria of IS NULL on each of the
fields you wish to check?

:

How can I create a report that will show only the field names that were left
blank on a record?

thank you.

NL
Click to expand...
Click to expand...
Click to expand...
 
Thank you.

SuzyQ said:
You could do something like this, although it would require frequent
compacting of database...But this might give you an idea.

Create a table, call it blankFields with RecordNumber (long) and FieldList
(text) as fields. Create a report and set the data source to that table. In
the on close and on open events put the following (air code)

The on Open event fills the blankFields table, the close event deletes the
records that it added.

Dim dteFreezeTime As Date

Private Sub Report_Close()

'delete the records added during this run of report
Dim strSQL As String

strSQL = "DELETE * "
strSQL = strSQL & "FROM [blankFields ] "
strSQL = strSQL & "WHERE [freezeTime] = #" & dteFreezeTime & "#"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

Private Sub Report_Open(Cancel As Integer)

Dim strSQL As String
Dim lngRecNo As Long
Dim strFields As String

Dim rs As DAO.Recordset

dteFreezeTime = Now()

strSQL = "SELECT TableName.F1, TableName.f2, TableName.f3, TableName.f4,
TableName.pk "
strSQL = strSQL & "FROM TableName "
strSQL = strSQL & "Where (TableName.F1 Is Null) OR (TableName.f2 Is
Null) OR (TableName.f3 Is Null) OR (TableName.f4 Is Null) "
strSQL = strSQL & "ORDER BY TableName.pk"

Set rs = CurrentDb.OpenRecordset(strSQL)

'initialize vars
strFields = "Fields: "

Do While Not rs.EOF
lngRecNo = rs!pk

If IsNull(rs!f1) Then
strFields = strFields & "F1"
End If

If IsNull(rs!f2) Then
If Len(strFields) = 7 Then
strFields = strFields & "F2"
Else
strFields = strFields & ", F2"
End If
End If
If IsNull(rs!f3) Then
If Len(strFields) = 7 Then
strFields = strFields & "F3"
Else
strFields = strFields & ", F3"
End If
End If
If IsNull(rs!f4) Then
If Len(strFields) = 7 Then
strFields = strFields & "F4"
Else
strFields = strFields & ", F4"
End If
End If

strSQL = "INSERT INTO blankFields (RecordNumber, FieldList,
freezeTime) "
strSQL = strSQL & "VALUES (" & lngRecNo & ", '" & strFields & "', #"
& dteFreezeTime & "#) "

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)

rs.MoveNext
Loop

Me.Filter = "[freezeTime] = #" & dteFreezeTime & "#"
Me.FilterOn = True

End Sub








nl said:
Maybe I'm not explaining myself well.
What I want is a report that would tell me the records that some fields are
missing information, only specifying the fields that are missing information.
Example. Record1, field State is missing, (something like that).
Record2, field zipcod is missing........
SuzyQ said:
Can you use the query as your record source and place an unbound text box on
the report for each field in the table. For each textbox have something like
this =iif(isnull([fieldname],"NULL","HAS DATA")). (or true/false, yes/no...
whatever. Your column headings can be the names of the fields, in the detail
section you will see whether or not the field has data or not. This may not
be very pretty, but it might give you the information you are looking for.

:

Yes, I did. but what I want it's a report, if there are 20 records, to show
only the field names that are blank for each record
:

Have you tried to run a query with the criteria of IS NULL on each of the
fields you wish to check?

:

How can I create a report that will show only the field names that were left
blank on a record?

thank you.

NL
Click to expand...
Click to expand...
Click to expand...
 
I just noticed I didn't reset the var strFields after writing to table. You
should add strFields = "Fields: " after writing to the table blankFields or
move the initializing that var just under lngRecordNumber var.

nl said:
Thank you.

SuzyQ said:
You could do something like this, although it would require frequent
compacting of database...But this might give you an idea.

Create a table, call it blankFields with RecordNumber (long) and FieldList
(text) as fields. Create a report and set the data source to that table. In
the on close and on open events put the following (air code)

The on Open event fills the blankFields table, the close event deletes the
records that it added.

Dim dteFreezeTime As Date

Private Sub Report_Close()

'delete the records added during this run of report
Dim strSQL As String

strSQL = "DELETE * "
strSQL = strSQL & "FROM [blankFields ] "
strSQL = strSQL & "WHERE [freezeTime] = #" & dteFreezeTime & "#"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

Private Sub Report_Open(Cancel As Integer)

Dim strSQL As String
Dim lngRecNo As Long
Dim strFields As String

Dim rs As DAO.Recordset

dteFreezeTime = Now()

strSQL = "SELECT TableName.F1, TableName.f2, TableName.f3, TableName.f4,
TableName.pk "
strSQL = strSQL & "FROM TableName "
strSQL = strSQL & "Where (TableName.F1 Is Null) OR (TableName.f2 Is
Null) OR (TableName.f3 Is Null) OR (TableName.f4 Is Null) "
strSQL = strSQL & "ORDER BY TableName.pk"

Set rs = CurrentDb.OpenRecordset(strSQL)

'initialize vars
strFields = "Fields: "

Do While Not rs.EOF
lngRecNo = rs!pk

If IsNull(rs!f1) Then
strFields = strFields & "F1"
End If

If IsNull(rs!f2) Then
If Len(strFields) = 7 Then
strFields = strFields & "F2"
Else
strFields = strFields & ", F2"
End If
End If
If IsNull(rs!f3) Then
If Len(strFields) = 7 Then
strFields = strFields & "F3"
Else
strFields = strFields & ", F3"
End If
End If
If IsNull(rs!f4) Then
If Len(strFields) = 7 Then
strFields = strFields & "F4"
Else
strFields = strFields & ", F4"
End If
End If

strSQL = "INSERT INTO blankFields (RecordNumber, FieldList,
freezeTime) "
strSQL = strSQL & "VALUES (" & lngRecNo & ", '" & strFields & "', #"
& dteFreezeTime & "#) "

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)

rs.MoveNext
Loop

Me.Filter = "[freezeTime] = #" & dteFreezeTime & "#"
Me.FilterOn = True

End Sub








nl said:
Maybe I'm not explaining myself well.
What I want is a report that would tell me the records that some fields are
missing information, only specifying the fields that are missing information.
Example. Record1, field State is missing, (something like that).
Record2, field zipcod is missing........
:

Can you use the query as your record source and place an unbound text box on
the report for each field in the table. For each textbox have something like
this =iif(isnull([fieldname],"NULL","HAS DATA")). (or true/false, yes/no...
whatever. Your column headings can be the names of the fields, in the detail
section you will see whether or not the field has data or not. This may not
be very pretty, but it might give you the information you are looking for.

:

Yes, I did. but what I want it's a report, if there are 20 records, to show
only the field names that are blank for each record
:

Have you tried to run a query with the criteria of IS NULL on each of the
fields you wish to check?

:

How can I create a report that will show only the field names that were left
blank on a record?

thank you.

NL
Click to expand...
Click to expand...
Click to expand...
 
Yeah, I figured there was an easier way. I just drew a blank as to how.

KenSheridan via AccessMonster.com said:
You should be able to do it with a simple function into which the values are
passed. Allowing for just three fields as an example:

Public Function GetNullFields(varCity, varState, varZip) As String

Dim strReturn As String

If IsNull(varCity) Then
strReturn = strReturn & "; City"
End If

If IsNull(varState) Then
strReturn = strReturn & "; State"
End If

If IsNull(varZip) Then
strReturn = strReturn & "; ZipCod"
End If

' remove leading semi colon and space
strReturn = Mid(strReturn, 3)

GetNullFields = "Missing fields: " & strReturn

End Function

In a query you'd then call the function like so:

SELECT MyID,
GetNullFields(City, State, ZipCod) AS MissingFields
FROM MyTable;

where MyID is the table's primary key.

It’s a simple task to extend the function to cover as many fields as you wish
simply by adding more arguments and extra If….End If constructs. If a field
allows zero length strings then you'd need to test for them as well as Null,
e.g.

If Nz(varCity, "") = "" Then
strReturn = strReturn & "; City"
End If

Also numeric fields might have a DefaultValue property of zero, which you
might also want to test for, e.g.

If Nz(UnitPrice, 0) = 0 Then
strReturn = strReturn & "; UnitPrice"
End If

Ken Sheridan
Stafford, England
Maybe I'm not explaining myself well.
What I want is a report that would tell me the records that some fields are
missing information, only specifying the fields that are missing information.
Example. Record1, field State is missing, (something like that).
Record2, field zipcod is missing........
Can you use the query as your record source and place an unbound text box on
the report for each field in the table. For each textbox have something like
Click to expand...
[quoted text clipped - 15 lines]
NL
Click to expand...
Click to expand...
Click to expand...
 
Back
Top