Need changes in Statement to get the correct balance amount ...

[Margaret]

Hi,

The following code works accurate when I don't care about the Date Order.

rs.Open "SELECT CustomerName, Option, FileNumber, City, Telephone, Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2 WHERE SD2.ID <= SD1.ID AND SD2.CustomerName= SD1.CustomerName) As Balance FROM SalesData SD1 Where CustomerName like '%" & txtSearchAnyWord & "%'ORDER BY CustomerName, ID", db

But I need all the dates in a particular order and the problem is that sometimes I need to enter data with old dates as well. The following code doesn't work properly showing wrong values in "Balance" Column. How to solve the problem? Please help me out.

rs.Open "SELECT CustomerName, Option, FileNumber, City, Telephone, Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2 WHERE SD2.DDate <= SD1.DDate AND SD2.CustomerName= SD1.CustomerName) As Balance FROM SalesData SD1 Where CustomerName like '%" & txtSearchAnyWord & "%'ORDER BY CustomerName, DDate", db

The attached file may help to understand my problem.

Regards,

Margaret