DCount method error

[Harry Gao]

I tried to do a similar CountIF method in Report, which
is DCount method: DCount(Expr, Domain, criteria).

However, when I select a select query name for the
Domain, it returns #Error. It works if I selected a table.
In the help, it said the Domain can be a table or query
name.

Anyone can help why?

 

[Rick Brandt]

I tried to do a similar CountIF method in Report, which
is DCount method: DCount(Expr, Domain, criteria).

However, when I select a select query name for the
Domain, it returns #Error. It works if I selected a table.
In the help, it said the Domain can be a table or query
name.

Are you sure you got the field name and criteria correct for the query? These
functions do not care whether the domain is a table or query.

 

[harry]

Hi, Rick:

Thank you for the suggestion. The field name and criteria
should be correct since I tried using the table, instead
of the query. It worked,but it gave all the data, which I
wanted to filter the group. Here is the statement:

=DCount("field1","qCATEGORY10","field1='Yes'")

Since I put it under a group footer, would it
automatically filter by that group? It looks like this
statement would not filter by group. If that is the case,
then it should give me incorrect count, not just an Error.
Any help?

 

[Rick Brandt]

Hi, Rick:

Thank you for the suggestion. The field name and criteria
should be correct since I tried using the table, instead
of the query. It worked,but it gave all the data, which I
wanted to filter the group. Here is the statement:

=DCount("field1","qCATEGORY10","field1='Yes'")

Since I put it under a group footer, would it
automatically filter by that group? It looks like this
statement would not filter by group. If that is the case,
then it should give me incorrect count, not just an Error.
Any help?

Count() would care whether it was in a group footer or header, DCount() does
not. Domain Aggregate function return the same no matter where you use them.

Is field1 a Yes/No field or a Text Field? If the former, you should test it for
True or False (without quotes). If it's text then your statement looks good to
me.

 

[Duane Hookom]

I think you can get rid of DCount() entirely. If you report's record source
is qCATEGORY10 then use
=Abs(Sum( [field1]= "Yes") )
This assumes Field1 is a text field. If it is a Yes/No field then use:
=Abs(Sum( [field1]) )

 

[Harry]

My gratitude to Duane, who suggested using ABS function.
It works!

Very happy.

-----Original Message-----
I think you can get rid of DCount() entirely. If you report's record source
is qCATEGORY10 then use
=Abs(Sum( [field1]= "Yes") )
This assumes Field1 is a text field. If it is a Yes/No field then use:
=Abs(Sum( [field1]) )
--
Duane Hookom
MS Access MVP

Hi, Rick:

Thank you for the suggestion. The field name and criteria
should be correct since I tried using the table, instead
of the query. It worked,but it gave all the data, which I
wanted to filter the group. Here is the statement:

=DCount("field1","qCATEGORY10","field1='Yes'")

Since I put it under a group footer, would it
automatically filter by that group? It looks like this
statement would not filter by group. If that is the case,
then it should give me incorrect count, not just an Error.
Any help?
correct
for the query? These

.