Count number of Zeros inputted in fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a table

Field 1 = 45
Field 2 = 57
Field 3 = 0
Field 4 = 0

Field 5 = "How many fields have a "zero" in them. Which would equal 2.

How can I go about setting up field 5. I am not sure on how to use the Count
Property properly. Thanks for your help!

Derek
 
Does the records in the table look like that

Field1 Field2 Field3 Field4
2 2 0 0

Or like
FieldName
2
2
0
0
 
Hi Derek,

I wouldn't store this information in a field, because one would
continually have to be re-calculating it as other field values changed.
Instead, use a calculated field in a query.

The COUNT() function is for counting records, not values in fields.
Something like this should do it, taking advantage of the fact in Access
and VBA, the value True is the same as -1:

CountOfZeroes: -1 * (([Field 1]=0) + ([Field 2]=0) + ([Field 3]=0) +
([Field 4]=0))
 
What I gave you should have worked, create a query and make the SQL as Follow

Select TableName.* , Abs(Field1=0)+Abs(Field2=0)+Abs(Field3=0)+Abs(Field4=0)
As Field5 From TableName

Copy this SQL to a new query, change the TableName , and the fields names to
match the names in the table.
 
Back
Top