Missing something simple in DCount

[anyoder]

I need help. I am trying to use DCount. I need to count occurances. my
formula is
Expr1: DCount([RETURNS_05],"Test"," > 0 And < 50"). I keep getting # Error
as my result.

Please advise


Below is an example of my table. The real table has 80,000 zipcodes, many
repeated.

Zipcode Returns
11111 23
12345 46
11111 200
33424 20
31111 32
11111 25

I need to count how many times returns >0 and <50 occurs.

Please advise.

 

[Allen Browne]

You need:
- quotes around the field name to count;
- to specify a field name in the criteria.

Example:
Expr1: DCount("[RETURNS_05]", "Test",
"[RETURNS_05] > 0 And [RETURNS_05] < 50")

That example would be equivalent to:
Expr1: DCount("*", "Test", "[RETURNS_05] Between 0 And 50")

 

[anyoder]

I got a number for each zipcode this time, however it is the same number for
all 80,000. For some reason it is still not working.

I need help. I am trying to use DCount. I need to count occurances.
my formula is

[quoted text clipped - 17 lines]

Please advise.

The first argument for DCount is a field name or expression in quotes. You
first argument is not in quotes and doesn't appear to be a field name either.
Looks more like the query's name.

The second argument is the name of a query or table in quotes. Your second
argument "Test" doesn't appear to be either unless the name of your query is
"Test".

The third argument is a valid SQL WHERE clause (without the word "where") that
can be applied to the query or table. Your third argument " > 0 And < 50" makes
no sense. WHAT is supposed to be greater than zero and less than 50?

Making a total WAG here I suspect you need...

Expr1: DCount("*", "[RETURNS_05]","returns > 0 And returns < 50").

 

[Rick Brandt]

I got a number for each zipcode this time, however it is the same
number for all 80,000. For some reason it is still not working.

Your original post said nothing about a count "per zip code". I thought you
wanted to count the rows with returns greater than zero and less than 50. If
you want a count per zip code then feed your existing query into another totals
query that groups on zip code and counts the rows between 1 and 49. DCount() is
not going to get you there.

 

[anyoder]

Ok if below is my Query

Zipcode Returns
11111 23
12345 46
11111 200
33424 20
31111 32
11111 27
31111 25

I need it to look like this:

Zipcode <50
11111 2
12345 1
33424 1
31111 2

Would I not use a DCount?

 

[Rick Brandt]

Ok if below is my Query

Zipcode Returns
11111 23
12345 46
11111 200
33424 20
31111 32
11111 27
31111 25

I need it to look like this:

Zipcode <50
11111 2
12345 1
33424 1
31111 2

Would I not use a DCount?

The domain functions incur a certain overhead per usage that makes them poor
choices to use in queries or code loops because that overhead is repeated for
every row in the query. They are only used in queries when some particular
effect makes them worth that ineeficiency.

Since a simple totals query will give you want you want there is no reason to
use DCount(). Your second query...

SELECT ZipCode, Count(*) as CountOfReturns
FROM FirstQueryName
WHERE Returns BETWEEN 1 AND 49
GROUP BY ZipCode