DCount: Counting Records Meeting Criteria

[Tina]

I have created a report where, depending upon the data,
the backcolor of the fields for a record changes.

When the enrollment for a school is less than the school's
building capacity, the backcolor for all the fields for
that school is green. When the enrollment is less than
the long range school campus capacity, the back color is
blue., etc. I have 4 different colors in all that the
backcolors can evaluate to.

I want to total the number of greens, blues, etc. to get a
count of the number of schools that are not crowded, more
crowded, etc.

I was trying to do this using dcount, but it isn't working.

This is what I tried:

DCount
("[txtBelowSBC]", "rptqryElemCapacity", "[txtBelowSBC]=1").

When I do this, I get an error message saying something
about a string being only 2000+ characters and the cursor
going to the last paren.

When I take out all the "" and just put the brackets, I
can preview the report, but I get an #error message in the
report, but not one from Access telling me what the error
is.

The query behind the report is "rptqryElemCapacity",which
is what I put in the DCount expression.

I created a field "txtBelowSBC" (also in the DCount
expression) in the query that is an expression which tells
me whether enrollment is less than the school building
capacity.

I tried to create one expression that returned a different
number for each of the 4 evaluations, so I wouldn't have
to have 4 different fields, but couldn't figure it out.

Any help or suggestions will be most appreciated.

Tina

 

[Ken Snell [MVP]]

DCount will give number of records from a table or a query, but not from a
form or report. However, because a form and report have a RecordSource,
which is a recordset, you can use the Recordsource reference in the DCount
function. Thus, try this (assumes that the report is open when the function
runs):

DCount("[txtBelowSBC]", Reports!rptqryElemCapacity.RecordSource,
"[txtBelowSBC]=1").

 

[Guest]

Ken:

Worked like a charm! Thanks!

Tina

-----Original Message-----
DCount will give number of records from a table or a query, but not from a
form or report. However, because a form and report have a RecordSource,
which is a recordset, you can use the Recordsource reference in the DCount
function. Thus, try this (assumes that the report is open when the function
runs):

DCount("[txtBelowSBC]", Reports! rptqryElemCapacity.RecordSource,
"[txtBelowSBC]=1").


--

Ken Snell
<MS ACCESS MVP>

I have created a report where, depending upon the data,
the backcolor of the fields for a record changes.

When the enrollment for a school is less than the school's
building capacity, the backcolor for all the fields for
that school is green. When the enrollment is less than
the long range school campus capacity, the back color is
blue., etc. I have 4 different colors in all that the
backcolors can evaluate to.

I want to total the number of greens, blues, etc. to get a
count of the number of schools that are not crowded, more
crowded, etc.

I was trying to do this using dcount, but it isn't working.

This is what I tried:

DCount
("[txtBelowSBC]", "rptqryElemCapacity", "[txtBelowSBC] =1").

When I do this, I get an error message saying something
about a string being only 2000+ characters and the cursor
going to the last paren.

When I take out all the "" and just put the brackets, I
can preview the report, but I get an #error message in the
report, but not one from Access telling me what the error
is.

The query behind the report is "rptqryElemCapacity",which
is what I put in the DCount expression.

I created a field "txtBelowSBC" (also in the DCount
expression) in the query that is an expression which tells
me whether enrollment is less than the school building
capacity.

I tried to create one expression that returned a different
number for each of the 4 evaluations, so I wouldn't have
to have 4 different fields, but couldn't figure it out.

Any help or suggestions will be most appreciated.

Tina

.

 

[Duane Hookom]

Wouldn't this be simpler with:
=Sum(Abs([txtBelowSBC]=1).

I wasn't aware of using a report's recordsource as the domain in DCount()...
very interesting.

--
Duane Hookom
MS Access MVP
--

Ken:

Worked like a charm! Thanks!

Tina

-----Original Message-----
DCount will give number of records from a table or a query, but not from a
form or report. However, because a form and report have a RecordSource,
which is a recordset, you can use the Recordsource reference in the DCount
function. Thus, try this (assumes that the report is open when the function
runs):

DCount("[txtBelowSBC]", Reports! rptqryElemCapacity.RecordSource,
"[txtBelowSBC]=1").


--

Ken Snell
<MS ACCESS MVP>

I have created a report where, depending upon the data,
the backcolor of the fields for a record changes.

When the enrollment for a school is less than the school's
building capacity, the backcolor for all the fields for
that school is green. When the enrollment is less than
the long range school campus capacity, the back color is
blue., etc. I have 4 different colors in all that the
backcolors can evaluate to.

I want to total the number of greens, blues, etc. to get a
count of the number of schools that are not crowded, more
crowded, etc.

I was trying to do this using dcount, but it isn't working.

This is what I tried:

DCount
("[txtBelowSBC]", "rptqryElemCapacity", "[txtBelowSBC] =1").

When I do this, I get an error message saying something
about a string being only 2000+ characters and the cursor
going to the last paren.

When I take out all the "" and just put the brackets, I
can preview the report, but I get an #error message in the
report, but not one from Access telling me what the error
is.

The query behind the report is "rptqryElemCapacity",which
is what I put in the DCount expression.

I created a field "txtBelowSBC" (also in the DCount
expression) in the query that is an expression which tells
me whether enrollment is less than the school building
capacity.

I tried to create one expression that returned a different
number for each of the 4 evaluations, so I wouldn't have
to have 4 different fields, but couldn't figure it out.

Any help or suggestions will be most appreciated.

Tina

.

 

[Ken Snell [MVP]]

Wouldn't this be simpler with:
=Sum(Abs([txtBelowSBC]=1).

probably....but if I'd answered the question with the above....

I wasn't aware of using a report's recordsource as the domain in DCount()...
very interesting.

....I wouldn't have been able to pass along this info!

)