Best method to catch blank fields

[nickadeemus2002]

Hello everyone. I am looking for advice on the best method to catch blank fields in my database. I have several fields that need to be populated with data, but I can not use the 'Required' property for data input. I want to be able to capture the blank field information with VBA code and generate a report that lists all the records that have blank fields, and what those fields are. I was thinking of writing a procedure using the IsNull method. But I was thinking it would be easier to use a routine that uses the If Then method. For example: IF Forms!Customers!State="" Then..... I also have this little function that can find the blank spaces-->
(Function IsBlank(V As Variant) As Boolean
On Error Resume Next
V = "" & V
If Len(V) = 0 Then IsBlank = True
End Function<--
But once the blank fields are found, I don't know how to prepare that data to make it into a report. Is there a better way to do this simple check for blank spaces? Any advice would be appreciated. Thanks

chris

 

[dcx693]

I can not use the 'Required' property for data input

Why not?

In any case, I'd check the tables, rather than the form because you can only reference one record at a time in your form. I'd probably run a query where I used "Is Null" as the critiera for each field (being sure to place then on different lines in the query grid to ensure they get applied as OR critiera - not AND). You can then use the query to feed your report.

 

[DALIEN51]

This Is One For A Query!

He is dead right this is one for a query on the table not code on the form! isnull on the criteria line as suggested is the way forward! good luck

DALIEN51

 

[Matty]

If you're looking for both nulls and empty strings, I sometimes use an Nz function in the query to change nulls to empty strings. Something like:

Nz(FieldName,"")

Then in the criteria part of the query, I'd check to see if it's "" (empty quotes). This is just off the top of my head, but you can always give that a shot.