Kelley:
If you are returning either "1" as a string or a zero-length string "" in
this column in the query rather than a numeric value or Null you might be
encountering a data-type mismatch, so try:
=Sum(IIf([Late]="1",1,0))/Count(*)
If the query is returning 1 or Null in the column then the same solution can
be used, but with the 1 as a numeric constant:
=Sum(IIf([Late]=1,1,0))/Count(*)
Note that to Count all rows as the divisor you can use an asterisk rather
than specifying a column. Only do the latter if you want to exclude Nulls
from the count.
Note also that you can only call aggregation operators like Sum or Count in
group or report footers, not in a page footer.
Another thing to note is that aggregation operators used like this in a
report act on all rows in the reports underlying recordset, i.e. the rows
returned by the query not necessarily the relevant rows in the source table,
as if the query joins tables there will be multiple instances of the rows
from the referencing table (that on the many side of a relationship) so the
same row will be counted more than once. In a case like that if you want to
count the number of distinct rows from the referencing table you can either
do so by incrementing an unbound control's value with code in the report's
module, by calling the DCount function to count the relevant rows directly in
the table, or by including a subquery in the SELECT clause of the report's
underlying query to count the rows and then binding a control to the column
returned by the subquery.
Ken Sheridan
Stafford, England
Kell2604 said:
Well originally this field had the word late in it if it the record was
considered "late" or it was blank if not. I realized that this may be part
of the problem and so I have changed the query so that there is simply a 1 in
the field if it is considered late or it is blank if not. So I need the
expression to sum the 1's in the column and divide by the total number of
records.
Thanks again!
Kelley
Jeff Boyce said:
Your expression appears to be adding up the values in the [LATE] field, then
dividing by the count of the [DaysDiff]. Since we don't know what you are
storing in these fields, it's tough to know what's wrong with the
expression.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
Kell2604 said:
Hi -
I am creating a report and I need to calculate a percentage.
I have tried my current formula a hundred different ways and get a syntax
error or #error in my report. Help!! I have the properties set to a
percentage.
=Sum([LATE])/Count([DaysDiff])
I need the percentage of LATE entries as compared to a count (TOTAL) of
entries in the DaysDiff column. The DaysDiff column has random numbers in
it, which is why I was using count instead of sum. Basically I need the
total number in the LATE column divided by the total number of records
(which
I was determining by counting the entries in the DaysDiff columns).
Thanks!!
