Count number of Zeros inputted in fields

S

sonofroy

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
 
O

Ofer Cohen

Does the records in the table look like that

Field1 Field2 Field3 Field4
2 2 0 0

Or like
FieldName
2
2
0
0
 
J

John Nurick

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))
 
O

Ofer Cohen

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.
 
Top