This one has me stumped, but I'll bet its pretty straight forward. I have a report in Access 2003 that shows points earned by employee over the past two years. This is too long. I want to run the report based on a date range that the user selects from a form. In order to do so, I need to show the balance forward on the report, i.e. I have a field in the report footer "=Sum([Points])" that gives me the total points for the report, but this is not accurate if the range is from Jan 08 to Dec 08 and there are points accumulated prior to Jan 08.

I thought a text box in the header with:

=DSum("[Points]","[CatchTable]","[CatchDate]=> Forms![frmSelectEmp]![StartDate] And [EmpNumber] = Forms![frmSelectEmp]![EmpNumber]")

But I just get an #Error.... Is there an expression that will give me the value I need?

Here's a good reference on the syntax:

http://www.mvps.org/access/general/gen0018.htm

And I suspect you want <.

Thank you for your quick reply. I have bookmarked that URL you sent, it was very helpful...however I am still getting #Error. I have amended my statement as follows:

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

Do you have any other suggestions? :confused:

That looks about right. EmpNumber is a numeric field? The form is open when the report opens, and the form/control names are spelled correctly? Can you post a sample db?

Hi Paul,

I found my problem. I was closing the frmSelectEmp when I should have made it not visible....silly mistake. It works now.

Thank you for your follow-up. With a few more tweaks, I got the statement working as I wanted it to. :)

No problem, glad you got it sorted out.