Syntax

[AshleyGreene]

I have designed a report in Access 97 and need help with
the syntax of a calculated field named [total]
Currently, it counts all the attendance hours in the
attendance_clock_in table for a particular student and
then subtracts that number from another field on the
report. This works fine.

=Format(Nz(DSum
("dailyhours","attendance_clock_in","studentid=" &
[employeeid]),0),"0.00")-[sumofhours]

However, what I want it to do is count only the attendance
hours in the attendance_clock_in table up to a particular
date that the user needs to get prompted to enter when the
report runs.

First, how would the syntax look to do that and if I
prompt the user to enter the ending date, can I use that
field when doing the calculation?

P.S. [dailyhours] is a number field in the
attendance_clock_in table, [studentID] is a number field
on the report and [employeeid] is a number field in the
table. [sumofhours] is another number field on the report.

 

[Jeff Boyce]

Ashley

One approach to calculating a value, based on selection criteria ("... up to
a date...") is to use a query. Can you create a query that returns the
information you're after? If so, you might be able to use that (query) as
the basis for your report?

 

[Jim/Chris]

Put as many of your calculations in the query as Jeff
wrote. Here is some guideline for calculation in a report
from Dirk Goldgar


(1) The summing text box must be in a group or report
footer or header
section. Usually it will be in a footer section. Don't
put it in a
page footer or header.

(2) The name of the summing text box must not be the same
as the name of
a field in the report's recordsource. So if your text box
is summing
the [Retail Price] field, as with the controlsource you
posted, name it
something like "txtTotalRetail".

(3) The argument of the Sum() function must be a field or
an expression
of fields in the report's recordsource; it can't be the
name of an
unbound or calculated control. If you have a calculated
control, for
example if [Retail Price] is a text box with controlsource
"=[WholesalePrice]+[Markup]", where [WholesalePrice] and
[Markup] are
fields in the recordsource, then you must repeat the
calculation in the
argument to the Sum() function, as with this controlsource:
"=Sum(WholesalePrice]+[Markup])".

Jim