DCount in query

  • Thread starter Thread starter Roger Epps
  • Start date Start date
R

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.
 
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
 
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 said:
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.
Click to expand...
.
Click to expand...
 
Well, the first thing that might help would be to post the SQL text of your query.


Roger said:
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
Click to expand...
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.
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top