Permenant Filed

[Ihthisham]

Balance: (SELECT SUM([InvoiceAmt]-[PaymentAmt])
FROM
tblTrans WHERE
tblTrans.Date<=btblTrans.Date
AND tblTrans.TnxNo<=btblTrans.TnxNo
AND tblTrans.CustomerID=btblTrans.CustomerID)





Condition is work & Getting the correct figure, But Cannot use its for report coz it's not a permanent field

Pls help me to Correct the cording to keep this is a permanent field

[June7]

Why not? Calculated field in query should be available to report.

[Ihthisham]

Running Sum.zip

[June7]

So are you trying to do a running sum? Maybe instead of subquery, use RunningSum property of textbox. If you want to provide db for analysis, follow instructions at bottom of my post.

[Ihthisham]

Running Sum.zipRunning Sum.zip

Pls Kindly Record or Runsum function for this cording

Balance: (SELECT SUM([InvoiceAmt]-[PaymentAmt])
FROM
tblTrans WHERE
tblTrans.Date<=btblTrans.Date
AND tblTrans.TnxNo<=btblTrans.TnxNo
AND tblTrans.CustomerID=btblTrans.CustomerID)

[June7]

As I said, use RunningSum property.

Report RecordSource:
SELECT Customer.*, tblTrans.* FROM Customer INNER JOIN tblTrans ON Customer.CustomerID = tblTrans.CustomerID;

Expression in textbox:
=[InvoiceAMT]-[PaymentAMT]

Textbox RunningSum property set to Yes.

[Ihthisham]

Tks For your effort,

My Access file was attached, Can you pls make the cording for me, Im very beginner to Access,

I hv try with coping your cording , but didt work.

[June7]

Well, I described the 3 simple things I did to your db. What exactly did you do? What does "didn't work" mean - error message, wrong result, nothing happens?

[Ihthisham]

Well, I described the 3 simple things I did to your db. What exactly did you do? What does "didn't work" mean - error message, wrong result, nothing happens?

Error message,

is its possible to edit my db Pls ?

[June7]

You should be able to accomplish these 3 simple changes. Step by step what I did.

1. opened your database

2. opened Running Balnce2 report in design view

3. changed RecordSource property to SQL statement already provided

4. changed ControlSource of Balance textbox to =[InvoiceAMT]-[PaymentAMT]

5. changed RunningSum property of Balance textbox to OverGroup (sorry, before I said change to Yes)