DCount()

  • Thread starter Thread starter Fipp
  • Start date Start date
F

Fipp

I am trying to use the DCount function in a report to
calculate a Yes/No field called [checkin]Where the value
= "True". The name of the report is "analysis" and the
name of the query that the report is based off of is
called "analquery"

If someone could show me an example of what the function
should look like typed up in this example I would greatly
appreciate it.
 
I am trying to use the DCount function in a report to
calculate a Yes/No field called [checkin]Where the value
= "True". The name of the report is "analysis" and the
name of the query that the report is based off of is
called "analquery"

If someone could show me an example of what the function
should look like typed up in this example I would greatly
appreciate it.
Click to expand...

If you are looking to display the count in a footer section of the report, then
place your textbox in that footer section and set its "Control Source" to:

=Abs(Sum([checkin]))

If you are looking to display a *total* value in a detail or header section, use
the DSum() function and, after placing a textbox on the appropriate section, set
its "Control Source" to:

=Abs(DSum("checkin","analquery"))

Setting the DSum()'s criteria section will also allow you to further control the
value returned as appropriate for your needs. If you need to include this
filter, post back with more information.
 
A Yes/No field has a value of either -1 or 0.
All you need do is sum the Yes values (and change the sign).
In an unbound Control on the report:
=Abs(Sum([CheckBoxfield]))

Look up the ABS() function in Access Help.

To count No values:
=Sum(IIf([CheckBoxField]=0,1,0))
 
-----Original Message-----
I am trying to use the DCount function in a report to
calculate a Yes/No field called [checkin]Where the value
= "True". The name of the report is "analysis" and the
name of the query that the report is based off of is
called "analquery"

If someone could show me an example of what the function
should look like typed up in this example I would greatly
appreciate it.
Click to expand...

If you are looking to display the count in a footer section of the report, then
place your textbox in that footer section and set its "Control Source" to:

=Abs(Sum([checkin]))

If you are looking to display a *total* value in a detail or header section, use
the DSum() function and, after placing a textbox on the appropriate section, set
its "Control Source" to:

=Abs(DSum("checkin","analquery"))

Setting the DSum()'s criteria section will also allow you to further control the
value returned as appropriate for your needs. If you need to include this
filter, post back with more information.
Click to expand...

Thanks so much for the help. That works great. Just out
of curiosity is it possible to write a function that
would preform a calculation on a text field but only with
a specific value? So for example the "results" report
contains a field [location]and a Yes/No field [checkedin]
and I want to count the number of times the location
field = "seam" and [checkedin] = "Yes"
Thanks so much for your help.

Fipp
 
Thanks so much for the help. That works great. Just out
of curiosity is it possible to write a function that
would preform a calculation on a text field but only with
a specific value? So for example the "results" report
contains a field [location]and a Yes/No field [checkedin]
and I want to count the number of times the location
field = "seam" and [checkedin] = "Yes"
Click to expand...

Sure. You can use the same approach, except you will be summing the results of
an expression in the first example:

(In a textbox in a group footer in the report)

=Abs(Sum([location] = "seam" And [checkedin] = True))

(For a *total* value in textbox in a detail or header section of the report -
watch for line wrap)

=Abs(DSum("*","analquery","[location] = ""seam"" And [checkedin] = True")
 
Back
Top