DCount in query

[Roger Epps]

I have a db with 3 fields that need to be counted.
"Primary","CurrHrs","Vol_Status".

So far I have used Count, DCount and IIF with no results
other than two of the fields being counted and one being
counted incorrectly.
The Primary field is a Yes/Null field and access2000 is
counting both the Y and the Null. I need to count only
the Y with the other field restrictions in place.

I am using the following Dcount in the Control Source
window of a bound box.

=DCount("[Primary]","Macvip","[currhrs] > 0 AND
[VOL_STATUS] >= 'RET'")

When this is used it counts the Primary fild Y/Null
together and produces the wrong result.

 

[John Spencer (MVP)]

That is probably because the Yes/Null field is actually a boolean field (Yes/No)
and therefore has a value of 0 (no) or -1 (Yes). Try adding that to your count.

=DCount("[Primary]","Macvip","[currhrs] > 0 AND
[VOL_STATUS] >= 'RET' AND [Primary] = -1")

In a Totals query you could also use
Abs(Sum(Primary))
as a calculated field to count the number of fields with Primary equal to Yes or
True or -1

 

[Roger Epps]

Thought maybe you had it John but it returns #Error.
Tell you this really has me stymied.
Will look at the Abs(Sum(Primary)) and see how it will
work with the other two fields.

-----Original Message-----
That is probably because the Yes/Null field is actually a boolean field (Yes/No)
and therefore has a value of 0 (no) or -1 (Yes). Try adding that to your count.

=DCount("[Primary]","Macvip","[currhrs] > 0 AND
[VOL_STATUS] >= 'RET' AND [Primary] = -1")

In a Totals query you could also use
Abs(Sum(Primary))
as a calculated field to count the number of fields with Primary equal to Yes or
True or -1

I have a db with 3 fields that need to be counted.
"Primary","CurrHrs","Vol_Status".

So far I have used Count, DCount and IIF with no results
other than two of the fields being counted and one being
counted incorrectly.
The Primary field is a Yes/Null field and access2000 is
counting both the Y and the Null. I need to count only
the Y with the other field restrictions in place.

I am using the following Dcount in the Control Source
window of a bound box.

=DCount("[Primary]","Macvip","[currhrs] > 0 AND
[VOL_STATUS] >= 'RET'")

When this is used it counts the Primary fild Y/Null
together and produces the wrong result.

.

 

[John Spencer (MVP)]

Well, the first thing that might help would be to post the SQL text of your query.

Still no luck with the query. Nothing seems to work
including the Abs(Sum(Primary)). That returns a #Error
also. Any other ideas?

-----Original Message-----
Thought maybe you had it John but it returns #Error.
Tell you this really has me stymied.
Will look at the Abs(Sum(Primary)) and see how it will
work with the other two fields.

-----Original Message-----
That is probably because the Yes/Null field is actually a boolean field (Yes/No)
and therefore has a value of 0 (no) or -1 (Yes). Try adding that to your count.

=DCount("[Primary]","Macvip","[currhrs] > 0 AND
[VOL_STATUS] >= 'RET' AND [Primary] = -1")

In a Totals query you could also use
Abs(Sum(Primary))
as a calculated field to count the number of fields

with
Primary equal to Yes or

True or -1

Roger Epps wrote:

I have a db with 3 fields that need to be counted.
"Primary","CurrHrs","Vol_Status".

So far I have used Count, DCount and IIF with no results
other than two of the fields being counted and one being
counted incorrectly.
The Primary field is a Yes/Null field and access2000 is
counting both the Y and the Null. I need to count only
the Y with the other field restrictions in place.

I am using the following Dcount in the Control Source
window of a bound box.

=DCount("[Primary]","Macvip","[currhrs] > 0 AND
[VOL_STATUS] >= 'RET'")

When this is used it counts the Primary fild Y/Null
together and produces the wrong result.
.

.