Count Empty Controls In A Forms. HELP

[bill crumpton]

I ahve a form that is based on a query. I would like to place an unbound control on the form to count a field on if it is blank. Any Help on the code please.

Thanks.

BAC

 

[Jack Cowley]

This has been a learning experience for me as I have not been able to get DCount to count empty or null records. The only solution that worked for me was the use DCount to get the total number of records and then subtract the the number of records that do have data in the field in question. This is the code that I used as the Control Source for the unbound field:

=DCount("AutoNumberField]","QueryName")-DCount("[FieldYouWantToCount]","QueryName")

Hope this is what you are after and maybe someone will come along and show us an easier way.... Maybe even R. Hicks!


[This message has been edited by Jack Cowley (edited 12-27-2000).]

 

[bill crumpton]

Thanks Jack, for the quick response. I'm sure this will work. There's always two ways to skin a cat, huh? By the way the response earlier reference the uppercase function on after update worked. Thanks.

BAC

 

[Jack Cowley]

I just read your question again, including the Topic. Now I am wondering if you are trying to count the blank fields for each control on the form, rather than a field from the query with no data. If what you want to do is show how many blank fields there are for each record then let me know as I have some code for that too....

 

[bill crumpton]

I was just gonna write you and let you know that the code didn't work for me. I am trying to count the blank fields on the form. Thanks again.

BAC

 

[Jack Cowley]

This will count the blank fields on a form. Put this code in the On Current event of the form.....

Dim ctl As Control
Dim frm As Form
Dim x As Integer
Set frm = Me

For Each ctl In frm.Controls
If IsNull(ctl) Then
x = x + 1
End If
Next ctl

Me![UnboundTextField] = x

Hope this is what you are after....

 

[bill crumpton]

Jack,
Thanks again, however I am not getting the right number. I have had a long day with this. Do I need to substitute any control names from your example other than the Me.[unboundtextfield] ? I am trying to count a text field on a query based form that is empty. The form is configured for continous form. Also the blank fields are not all in a row, there are some populated fields intermingled. I am just trying to give you as much to go on as you can. Any and all help is definitley appreciated. I am leaving office now, but when I get home I'll log back on if you happen to get anything. Thanks for all your time.

Bill