I have the following query:
tblDraws_Details1.ID, tblRepayment.DrawIDrpmt, tblDraws_Details1.Amount, tblRepayment.Amount, tblRepayment.PaymentMadeYN, DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] & " and PaymentMadeYN = '" & [PaymentMadeYN] & "'")+DSum("Amount","tblRePayment","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = 'Yes'") AS balance
tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt;
The balance doesnt show anything though. Its NULL.
What I am wanting to do is display a balance according to if the [PaymentMadeYN] (its a text) is 'Yes' or 'No'. If yes then tblDraws_details.amount + tblRepayment.amount (repayment amount is a negative number) but if 'No' then do not add that repayment amount.
I have this query in a form and it works but I am trying to make a separate query i/o having it in a form.
I have attached a db with 2 tables and they query.
thank you for your help. Greatly appreciated.