# DCount

A

#### an

Hello!

I have a table with 3 columns with number data.
I need construct a query to Count the equals values in the
3 columns.
Ex: ColA ColB ColC
0 0 0
Result=3

To single Col I used in field query:

DCount("ColA";"Table";"ColA=0")
And work fine, but to next column I don't know how do it.

an

J

#### John Spencer (MVP)

If the three columns ALWAYS have a value then:

SELECT Abs(ColA=ColB + ColA=ColC + ColB=ColC)
FROM Table

If the columns could be blank (null), what do you want to call a match. Is 0
equal to null? Is Null equal to Null?

A

#### an

Thanks for your help, but don't work fine.
Thanks.
an

J

#### John Spencer (MVP)

I'm sorry. I do not understand.

Did my suggestion work? Or did it fail?

A

#### an

I'm sorry, too.

questionable, Ok?

The problem is wich I have blank cells (nulls) in table
and zeros. If I count Column by Column, it's all right.
But I need to count totals zeros (not nulls values) in 3
Columns of Table.
For example, if A1=3, B1=0 and C1=0 the query count only
the value in B1 but not count 2nd zero inC1. The result=1
were the exact result is 2.

Not count seconds values independentment of position.
I used in query:
Count0: DCount("[IdNumber]";"T_Table";"[ColumnA]=0 or
[ColumnB]=0 or [ColumnC]=0")

and result=1. Why?

Sorry for my explanation, but...

Cordially
an

M

#### Michel Walsh

Hi,

Your DCount returns only 1 because there is only ONE RECORD where the condition hold.

Try the initial John's suggestion, but modified like this:

SELECT CHOOSE( 1+ Abs(ColA=ColB + ColA=ColC + ColB=ColC) , 1, 2, 3, 3)
FROM TableNameHere

So, by implicit enumeration (not really by logic) the result is always right.

Hoping it may help,
Vanderghast, Access MVP

I'm sorry, too.

questionable, Ok?

The problem is wich I have blank cells (nulls) in table
and zeros. If I count Column by Column, it's all right.
But I need to count totals zeros (not nulls values) in 3
Columns of Table.
For example, if A1=3, B1=0 and C1=0 the query count only
the value in B1 but not count 2nd zero inC1. The result=1
were the exact result is 2.

Not count seconds values independentment of position.
I used in query:
Count0: DCount("[IdNumber]";"T_Table";"[ColumnA]=0 or
[ColumnB]=0 or [ColumnC]=0")

and result=1. Why?

Sorry for my explanation, but...

Cordially
an

A

#### an

My option:

DCount+Dcount+Dcount

Work Fine.
Many Thanks.
an
-----Original Message-----
Hi,

Your DCount returns only 1 because there is only ONE
RECORD where the condition hold.
Try the initial John's suggestion, but modified like this:

SELECT CHOOSE( 1+ Abs(ColA=ColB + ColA=ColC + ColB=ColC) , 1, 2, 3, 3)
FROM TableNameHere

So, by implicit enumeration (not really by logic) the result is always right.

Hoping it may help,
Vanderghast, Access MVP

I'm sorry, too.

questionable, Ok?

The problem is wich I have blank cells (nulls) in table
and zeros. If I count Column by Column, it's all right.
But I need to count totals zeros (not nulls values) in 3
Columns of Table.
For example, if A1=3, B1=0 and C1=0 the query count only
the value in B1 but not count 2nd zero inC1. The result=1
were the exact result is 2.

Not count seconds values independentment of position.
I used in query:
Count0: DCount("[IdNumber]";"T_Table";"[ColumnA]=0 or
[ColumnB]=0 or [ColumnC]=0")

and result=1. Why?

Sorry for my explanation, but...

Cordially
an
-----Original Message-----
I'm sorry. I do not understand.

Did my suggestion work? Or did it fail?

.

.