Syntax

  • Thread starter Thread starter AshleyGreene
  • Start date Start date
A

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.
 
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?
 
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
 
Back
Top