Need Help with Format syntax

[connieharper]

I have a report designed in Access 97. There are several caculated fields on this report. I am having trouble with this one.

1. Total Hours To Date

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

How the report works: I run this report using a macro in which I first run a make table query in which I prompt the user for a range of dates. Based on the dates entered, a table is created contained attendance entries. Then I print the report which runs off a second query that uses the table I made in the first query.

Problem: Total Hours to Date is a field on the report. The "format" syntax I am using works great except the value it comes up with includes all hours entered for the student. It only needs to include hours entered for the student up to the ending date that was entered when the user was prompted for a range of dates.

Can someone help?

 

[Pat Hartman]

A more efficient method of obtaining sums is to do them in the report itself. If the detail necessary to do th sums is not included in the detail for the report, then use a totals query and join that to your current query.

Totals query:
Select studentid, Sum(Nz(dailyHours,0)) As SumDailyHours
From attendance_clock
Group By studentid;

Join the above query to the report's recordsource query on studentid.