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
blank on a record?
thank you.
NL
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
SuzyQ
Have you tried to run a query with the criteria of IS NULL on each of the
fields you wish to check?
fields you wish to check?
N
nl
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
only the field names that are blank for each record
S
SuzyQ
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.
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.
N
nl
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........
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
S
SuzyQ
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
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
N
nl
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
S
SuzyQ
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.
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
S
SuzyQ
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........
[quoted text clipped - 15 lines]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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.