Count specific records in subreport / DCount

[Attaroa]

Hi All,

I am new to Access and VBA and I am having trouble getting a count in a subreport.

I have a table named "companies" and a query named "clients" The clients are linked to our different companies. Both have a field named "country" which is linked to a table with all the existing countries.

I have created a report based on the "companies" table. The subreport is based is the "client" query and shows all clients for each company. So far so good.

In the report footer I want to create a count for all clients that are based in "Netherlands" and all clients that are not.

I tried: =DCount("[country]";"[client]";"[country] = 'Netherlands' ")
This doesn't work. This returns #error. I tried with brackets, without brackets, comma's etc, but all return the same.

I have also tried:
=Som(Abs([country]="Netherlands"))
and
=Som(IIf([country]="Netherlands";1;0))
these return the following message:

"This expression has a spelling error or is too complex for evaluation.
A numeric expression could contain to many complex elements.
Try to simplify the expression by assigning several parts to variables."

What am I doing wrong?

[Basenji3]

It may be as simple as using SUM.

[Attaroa]

Thanks for the response. I tried:

=Sum(Abs([country]="Netherlands"))
and
=Sum(IIf([country]="Netherlands";1;0))

these return the following message:

"This expression has a spelling error or is too complex for evaluation.
A numeric expression could contain to many complex elements.
Try to simplify the expression by assigning several parts to variables."

[nico5038]

I would add a field in the query with '1' when Dutch and '0' when not, like you posted.

select ...., IIf([country]="Netherlands";1;0) as Nederlands, ... from ...
 

Now the solution is to add the field in the group footer like:
=Sum([Nederlands])

Nic;o)