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

Thanks in advance
an

Ad

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?
The solution becomes more complex depending on your answers.

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.

Don´t work my query but about your suggestion is not
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.

Don´t work my query but about your suggestion is not
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

Ad

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.

Don´t work my query but about your suggestion is not
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?

.

.

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.