Transactions before a user input date

[Guest]

Hi,

I have built a report that lists transactions for an account # between
certain dates that are entered by the user.

I have a planned budget field that is the inital amt for that account.
At the end of the report I get a balance of all my transactions for that
period then subtract it from my planned budget. My problem is that it only
subtracts my transactions from the specific dates entered by the user, though
transactions before this date effect the planned budget amt.

Is there some way that I can accumulate the previous transactions before the
beggining date entered by the user and then subtract this amount off the
planned budget as well to give me a more precise total?

 

[Terry]

Tina,

A Dsum function (see help) will sum a field in a recordset
beyond what is in your report's record source. Be careful
with the criteria portion and check you are only summing
what you want to sum.

Once you have the dsum calculation as you want it,
subtract it from your budget figure or whatever.

HTH

Terry

 

[Guest]

Hi Terry,

Thanks for your reply. I created a calculated control txt field on my main
report as the following:
=DSum("[InvoiceAmt]","tbl_Invoices","[InvoiceDate] < [forms]![Report
DateRange]![Beginning Trans Date]")

This works but I only want to know the total amt per group and it gives me
the total off all the Invoices < [forms]![Report DateRange]![Beginning Trans
Date] for every group.
For ex: It gives me at total of $8000 which is the total of all the
invoices before that date but should only display $2000 for that group. I
have put the txt field in the group header.


Thanks!