Does your table have a primary key - a unique value that identifies one
specific record? If so, you could use a vba routine to return the null fields.
Copy the function below and paste it into a VBA module. Then call it in a
query something like the following - assumes that your primary key field is a
number value.
Field: ListBlanks: fListBlanks("SELECT * FROM [YourTableName] WHERE
[PrimaryKeyfield]= " & [PrimaryKeyField] )
You can choose to list the specific fields instead of using the all fields
operator. You can choose to use other criteria to specify the record you want
to check, but you will need to make sure your criteria specify only one record
or you will get erroneous results.
One thing to note about the function as written is that it will not detect
boolean (yes/no) fields as blank since they always have a value (true or
false) and it will not detect fields that have zero automatically assigned as
the default value.
I would also add identifier fields so you know which record is being returned.
So the SQL of your query might look like the following:
SELECT PkField, LastName, FirstName
, ListBlanks: fListBlanks("SELECT * FROM [SomeTable] WHERE [PkField]= " &
[PkField] )
FROM [SomeTable]
Public Function fListBlankFields(strQuery As String)
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Long
Dim strReturn As String
Set rs = CurrentDb().OpenRecordset(strQuery)
If rs.RecordCount > 0 Then
For i = rs.Fields.Count - 1 To 0 Step -1
If Len(rs.Fields(i) & "") = 0 Then
strReturn = strReturn & ", " & rs.Fields(i).Name
End If
Next i
Else
strReturn = ", No Matching Record"
End If
If Len(strReturn) = 0 Then
fListBlankFields = "None"
Else
fListBlankFields = Mid(strReturn, 3)
End If
End Function
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Ok, that helps, but it gives me the resuls with no space between fields, how
I add the space? Ex. statezipcode. Would this work with more than 40
fields...?
Allen Browne said:
You could put a text box on the report, and set its Control source to an
expression like this:
=IIf([State] Is Null, "State ", Null) & IIf([ZipCode] Is Null, "Zip",
Null) & ...
The text box will then list the names of the fields that are null.
If you have any problems, these further suggestions may help:
a) Make sure the name of this text box is not the same as the name of a
field (e.g. it must not be called State or ZipCode.) Access gets confused if
the Name property is the same as a field, but it's bound to something else.
b) You may need to have text boxes for State, ZipCode etc on your report.
You can set their Visible property to No, but in some cases if you don't
have the text boxes, Access doesn't bother fetching the data.
c) A field named "Name" may not work. Nearly everything in Access has a Name
property (including reports), so Access may think you mean the name of the
report (which is never null) rather than the contents of the Name field.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I would like a report that would tell me the records that some fields are
missing information, only specifying the fields that are missing
information.
Example. Fields: Name; State; ZipCode, etc. The report would read:
Record1, field State is missing, (something like that).
Record2, field zipcod is missing........
Record3, fiel state and zipcode missing.....
thank you.
