Dsum problem

[rudolfelizabeth]

SELECT Transactions.Number, Transactions.startdate, Transactions.stopdate, Transactions.Balance, DSum("[Balance]","transactions"," [Transactions]![startdate] <=#" & [startdate] & "#") AS Beginbalance
FROM Transactions
GROUP BY Transactions.Number, Transactions.startdate, Transactions.stopdate, Transactions.Balance
HAVING (((Transactions.Number)=[Your number ?]) AND ((Transactions.startdate)=[Your startdate ?]) AND ((Transactions.stopdate)=[Your stopdate ?]))
ORDER BY Transactions.startdate;

In query design view you must activate the total
and choose groupby or it won't execept the statement.

what I trying to solve is to use this query later on with another query in a report to produce a statement of account.


I have search the internet there seems to be none available.
I have call and talked with different users no one seems to have a solution to put this report together via query design view in ms access. I am trying to solve this statement of account problem in an easy way that everybody understands how its done because a lot of users are asking for it.

I have tried and tested out different solutions offered via the internet none seem to work properly.

I had the same problem with the runsum on a from your site was the only one to provide me with the correct solution.

your help will be appreciated.

 

[PHV]

What do you want to do that's not working for you ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rudolfelizabeth]

The statement above must provide the value for the beginbalance(the Previous Balance for the report.)
the user asked when he enters the startdate,
the value must be all the records of the account for the user input, <= the startdate.(it doesnot produce any result with the criteria part, when I take out the criteria part it produces the sum over all the transactions )

Then I will ran another query to produce the current transactions
that means between startdate and stopdate.

lay out of report

value of Dsum beginbalance 2000.00

transactions charges 100.00 2100.00
and payment 1100.00 1000.00
Charge 50.00 1050.00
and so on

In the Header you get

Previuos balance (beginbalance 2000.00)
Total charges 150.00
Total of payments 1100.00
Closing balance 1050.00