Calculation

[integritycare]

Hi again,

Have a requirement to have a running balance which incorporates both deposits and debits.

I have used this code for the deposits, but not sure how to incorporate the debits

Balance: (SELECT Sum(tblBankRegister.TDeposit) AS Balance

Many thanks

Integrity

 

[PHV]

I'd use something like this:
Balance: (SELECT Sum(TDeposit)-Sum(TDebit) FROM daTable WHERE someCondition) AS Balance

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

 

[integritycare]

Hi PHV,
Many thanks for your reply. But I'm not that good at writing WHERE clauses. I did try, but the end result was no balance showing.
Could you give me some idea how to do this.

Many thanks,

Integrity

 

[PHV]

What is your actual SQL code ?

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

 

[integritycare]

Hi PHV,
Thank you for your reply.
The code for the query is

SELECT T1.BankR, T1.DateRcvd, T1.TDeposit, T1.TDebit, T1.Memo, T1.ChqNo, T1.CHK, T1.AcctNo, T1.AcctName, (SELECT Sum(tblBankRegister.TDeposit) AS Balance
   FROM tblBankRegister
   WHERE tblBankRegister.BankR <= T1.BankR) AS Balance
FROM tblBankRegister AS T1
WHERE (((T1.DateRcvd) Between [forms]![frmGeneralLedger]![txtStartDate] And [forms]![frmGeneralLedger]![txtEndDate]));

I have looked at the where clause but was not sure how to do it.

Many Thanks,,Integrity

 

[integritycare]

Hi PHV,
I have written some code which seems to work quite well. Can you look at it for me and tell me if it is correct.

SELECT T1.BankR, T1.DateRcvd, T1.BrokerStaffID, T1.TDeposit, T1.TDebit, T1.Memo, T1.ChqNo, T1.CHK, T1.AcctNo, T1.acType, T1.AcctName, (SELECT Sum(TDeposit)-Sum(TDebit) 
   FROM tblBankRegister
   WHERE tblBankRegister.BankR <= T1.BankR) AS Balance
FROM tblBankRegister AS T1
ORDER BY T1.BankR;

Many Thanks,

Integrity

 

[PHV]

Does TDeposit or TDebit allow null value ?

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

 

[integritycare]

HI PHV

Both TDeposit and TDebit have their Default Value set to "0 in the table, so then no, they don't allow default values.

Regards,

Integrity

 

[integritycare]

Hi PHV

Sorry!
Both TDeposit and TDebit have their Default Value set to "0 in the table, so then no, they don't allow Null values.

Regards,

Integrity