Balance forward + DSum

[Opal]

I need some help in trying to create a balance forward
field on a report. I am running Access 2003 and I
want to be able to produce a report by employee
number to show total "points" accumulated. I want
the employee to be able to select a date range for
the report (i.e. from the first of the month) and
have a balance forward field on the report that
shows the total accumulate points up until the first
date on the report. I have tried DSum as follows:

=DSum("Points","CatchTable","[EmpNumber] =
" & Forms!frmSelectEmp!EmpNo & " AND
[CatchDate] <= #" & Forms!frmSelectEmp!StartDate & "#")

and I get # Name? as my result. What am I missing
or should I be writing a subquery. I am at a loss
as to how to proceed to get what I need.

 

[Opal]

Found the solution:

=DSum("[Points]","CatchTable","[EmpNumber] =
" & Forms!frmSelectEmp!EmpNo & " AND
[CatchDate] <= #" & Forms!frmSelectEmp!StartDate & "#")

 

[Duane Hookom]

Opal,
Is EmpNumber numeric or text?
Is the name of your text box the same as the name of a field?
Does the form stay open after the report opens?

I try not to use domain aggregate function and would suggest you try a
subquery in your report's record source.

 

[Opal]

Hi Duane,

The EmpNumber is numeric.
The name of the field and text box differ.
I keep the form open (but .visible = false) and close when the report
closes.

I thought a sub-query might be more advantageous but couldn't
quite get my head around creating it. I have made sub-queries in
the past and have studied Allen Browne's website, but couldn't
find anything to help me determine the sum of a field up to the
date range specified in the form.