If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Reducing Balance on Invoice
Within one form (Invoice) I am trying to record payments which reduce the
balance oustanding Within the Invoice form have a subform (datasheet view) which list a number of future payments that are due. The number of payments to bne made vary with each invoice. The Subform is Date Due - Payment Due - Balance Due. Balance Due Field is currently formulated as [Forms]![Invoice]![Total] -Payment Due This of course works correctly only with the first [Balance Due]. I can sum the total [Payments Due] but I cannot fathom how to show a "staged" reducing balance without using an untidy bunch of pre-labled fields Payment1, Payment2 etc. Any sugestions please? Brian |
#2
|
|||
|
|||
Reducing Balance on Invoice
Brian,
If I understand correctly... Using some sample data on the Main form. (LoanID ia a unique autonumber) LoanID Total 174 500 On the Sub, related Parent to Child via LoanID... (PmtID is a unique autonumber for each payment) LoanID PmtID PmtDate PmtAmt Bal 174 122 1/1/10 50 450 174 128 2/1/10 50 400 174 137 3/1/10 50 350 etc.... The Bal calcualtion would be =Forms!Invoice!Total - DSum("[PmtAmt]","tblPayments","LoanID = " & LoanID & " and PmtID = "& PmtID) Couldn't test exactly, so please check my syntax. But... that's the idea. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Within one form (Invoice) I am trying to record payments which reduce the balance oustanding Within the Invoice form have a subform (datasheet view) which list a number of future payments that are due. The number of payments to bne made vary with each invoice. The Subform is Date Due - Payment Due - Balance Due. Balance Due Field is currently formulated as [Forms]![Invoice]![Total] -Payment Due This of course works correctly only with the first [Balance Due]. I can sum the total [Payments Due] but I cannot fathom how to show a "staged" reducing balance without using an untidy bunch of pre-labled fields Payment1, Payment2 etc. Any sugestions please? Brian |
#3
|
|||
|
|||
Reducing Balance on Invoice
Hi AL
Many thank for your suggestion. Not quite solved the problem but a step in the right direction I think Your example was this But the result in my subform was that each PmtAmt Left a balance that was equal to the total start amount less that particular deduction. For example assuming start amount on the main invoice form was £500 LoanID PmtID PmtDate PmtAmt Bal 174 122 1/1/10 50 450 174 128 2/1/10 150 350 174 137 3/1/10 75 425 Any thoughts on a tweak? Brian "Al Campagna" wrote: Brian, If I understand correctly... Using some sample data on the Main form. (LoanID ia a unique autonumber) LoanID Total 174 500 On the Sub, related Parent to Child via LoanID... (PmtID is a unique autonumber for each payment) LoanID PmtID PmtDate PmtAmt Bal 174 122 1/1/10 50 450 174 128 2/1/10 50 400 174 137 3/1/10 50 350 etc.... The Bal calcualtion would be =Forms!Invoice!Total - DSum("[PmtAmt]","tblPayments","LoanID = " & LoanID & " and PmtID = "& PmtID) Couldn't test exactly, so please check my syntax. But... that's the idea. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Within one form (Invoice) I am trying to record payments which reduce the balance oustanding Within the Invoice form have a subform (datasheet view) which list a number of future payments that are due. The number of payments to bne made vary with each invoice. The Subform is Date Due - Payment Due - Balance Due. Balance Due Field is currently formulated as [Forms]![Invoice]![Total] -Payment Due This of course works correctly only with the first [Balance Due]. I can sum the total [Payments Due] but I cannot fathom how to show a "staged" reducing balance without using an untidy bunch of pre-labled fields Payment1, Payment2 etc. Any sugestions please? Brian . |
#4
|
|||
|
|||
Reducing Balance on Invoice
Brian,
Whenever you have a problem with code, cut and paste exactly the code you used into your reply. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Hi AL Many thank for your suggestion. Not quite solved the problem but a step in the right direction I think Your example was this But the result in my subform was that each PmtAmt Left a balance that was equal to the total start amount less that particular deduction. For example assuming start amount on the main invoice form was £500 LoanID PmtID PmtDate PmtAmt Bal 174 122 1/1/10 50 450 174 128 2/1/10 150 350 174 137 3/1/10 75 425 Any thoughts on a tweak? Brian "Al Campagna" wrote: Brian, If I understand correctly... Using some sample data on the Main form. (LoanID ia a unique autonumber) LoanID Total 174 500 On the Sub, related Parent to Child via LoanID... (PmtID is a unique autonumber for each payment) LoanID PmtID PmtDate PmtAmt Bal 174 122 1/1/10 50 450 174 128 2/1/10 50 400 174 137 3/1/10 50 350 etc.... The Bal calcualtion would be =Forms!Invoice!Total - DSum("[PmtAmt]","tblPayments","LoanID = " & LoanID & " and PmtID = "& PmtID) Couldn't test exactly, so please check my syntax. But... that's the idea. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Within one form (Invoice) I am trying to record payments which reduce the balance oustanding Within the Invoice form have a subform (datasheet view) which list a number of future payments that are due. The number of payments to bne made vary with each invoice. The Subform is Date Due - Payment Due - Balance Due. Balance Due Field is currently formulated as [Forms]![Invoice]![Total] -Payment Due This of course works correctly only with the first [Balance Due]. I can sum the total [Payments Due] but I cannot fathom how to show a "staged" reducing balance without using an untidy bunch of pre-labled fields Payment1, Payment2 etc. Any sugestions please? Brian . |
#5
|
|||
|
|||
Reducing Balance on Invoice
My apologies,
I created a PaymentID as suggested. The full code in the [Balance] field is as follows - =Forms!fmInvoice!Text24-DSum("[AmountDue]","tbPaymentTerms","PaymentID = " & [PaymentID] & " and PaymentID = " & [PaymentID]) Text24 is the invoice value shown on the main form. I was getting an error code but that was caused by = instead of = Result of the above formula is - eg Total on Invoice= £3000 PaymentID 1 Amount £1,000 Balance £2000 (ie £3000 - £1,000) PaymentID 2 Amount £1,500 Balance £1500 (ie £3,000 - £1500) Brian "Al Campagna" wrote: Brian, Whenever you have a problem with code, cut and paste exactly the code you used into your reply. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Hi AL Many thank for your suggestion. Not quite solved the problem but a step in the right direction I think Your example was this But the result in my subform was that each PmtAmt Left a balance that was equal to the total start amount less that particular deduction. For example assuming start amount on the main invoice form was £500 LoanID PmtID PmtDate PmtAmt Bal 174 122 1/1/10 50 450 174 128 2/1/10 150 350 174 137 3/1/10 75 425 Any thoughts on a tweak? Brian "Al Campagna" wrote: Brian, If I understand correctly... Using some sample data on the Main form. (LoanID ia a unique autonumber) LoanID Total 174 500 On the Sub, related Parent to Child via LoanID... (PmtID is a unique autonumber for each payment) LoanID PmtID PmtDate PmtAmt Bal 174 122 1/1/10 50 450 174 128 2/1/10 50 400 174 137 3/1/10 50 350 etc.... The Bal calcualtion would be =Forms!Invoice!Total - DSum("[PmtAmt]","tblPayments","LoanID = " & LoanID & " and PmtID = "& PmtID) Couldn't test exactly, so please check my syntax. But... that's the idea. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Within one form (Invoice) I am trying to record payments which reduce the balance oustanding Within the Invoice form have a subform (datasheet view) which list a number of future payments that are due. The number of payments to bne made vary with each invoice. The Subform is Date Due - Payment Due - Balance Due. Balance Due Field is currently formulated as [Forms]![Invoice]![Total] -Payment Due This of course works correctly only with the first [Balance Due]. I can sum the total [Payments Due] but I cannot fathom how to show a "staged" reducing balance without using an untidy bunch of pre-labled fields Payment1, Payment2 etc. Any sugestions please? Brian . . |
#6
|
|||
|
|||
Reducing Balance on Invoice
On Fri, 12 Mar 2010 14:20:05 -0800, Brian
wrote: My apologies, I created a PaymentID as suggested. The full code in the [Balance] field is as follows - =Forms!fmInvoice!Text24-DSum("[AmountDue]","tbPaymentTerms","PaymentID = " & [PaymentID] & " and PaymentID = " & [PaymentID]) You're mistaking the meaning of the Boolean Algebra operator AND. It looks like the English language conjunction, but it isn't! AND is an operator in Boolean (true/false) logic, just like + is an operator in arithmatic. A AND B is TRUE if both A and B are TRUE; it is FALSE if either or both are false. So the statement PaymentID = 2 AND PaymentID = 2 will be TRUE if PaymentID is 2; it will be FALSE for any other value. If you want to see the sum of the amount due for all values of paymentID up to and including the one in the query, just use "PaymentID = " & [PaymentID] If not, please explain which payment ID's you want to sum. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Reducing Balance on Invoice
Hello John, Thanks for helping
I re-entered your formula and whilst that does produce a reducing balance on the first record (Invoice#1) the total payments are carried forward to Invoice#2 I m trying to achieve the followin Main Form [fmInvoice] Value is in [Text24]. Link Field is [InvoiceID] Subform=[fmPaymentTerms] Fields InvoiceID], [PaymentID], [AmountDue],[DateDue],[BalanceDue] The Subform should show the schedule of paymnets with a reducing balance. Example assuming Text24 Value = £1500.00 Subform- InvoiceID Payment ID Date Due Amount Due Balance Due 1000 001 01 March £500.00 £1000.00 1000 002 01 April £600.00 £ 400.00 1000 003 01 May £400.00 £ 0.00 Previous attempts hjave resulted in data in Record (invoice#1) spilling into subsequent Records. Brian "John W. Vinson" wrote: On Fri, 12 Mar 2010 14:20:05 -0800, Brian wrote: My apologies, I created a PaymentID as suggested. The full code in the [Balance] field is as follows - =Forms!fmInvoice!Text24-DSum("[AmountDue]","tbPaymentTerms","PaymentID = " & [PaymentID] & " and PaymentID = " & [PaymentID]) You're mistaking the meaning of the Boolean Algebra operator AND. It looks like the English language conjunction, but it isn't! AND is an operator in Boolean (true/false) logic, just like + is an operator in arithmatic. A AND B is TRUE if both A and B are TRUE; it is FALSE if either or both are false. So the statement PaymentID = 2 AND PaymentID = 2 will be TRUE if PaymentID is 2; it will be FALSE for any other value. If you want to see the sum of the amount due for all values of paymentID up to and including the one in the query, just use "PaymentID = " & [PaymentID] If not, please explain which payment ID's you want to sum. -- John W. Vinson [MVP] . |
#8
|
|||
|
|||
Reducing Balance on Invoice
Brian,
When using code suggested by any respondent, you need to convert the example object names to your own. Form names, control names.. etc... You wrote on your 3/12 response... =Forms!fmInvoice!Text24-DSum("[AmountDue]","tbPaymentTerms","PaymentID = " & [PaymentID] & " and PaymentID = " & [PaymentID]) That's not what I suggested... ***My DSum involved two logical requirements. 1. That only payment amounts associated with that particular Loan (InvoiceID) be summed. 2. Only Payments made "up to" the current record be included in the summing. PaymentID = PaymentID Your code incorrectly states the "up to" PaymentID logic, and doesn't include any logic at all for which Payment values are associated with a particular loan. (= InvoiceID) You wrote on your 3/13 response... I re-entered your formula and whilst that does produce a reducing balance... I'll repeat... when you have trouble with code always cut & paste that code in your reply. Right now, we don't know what code you currently have, and where you've made a mistake. I also question your naming convention. Text24 is not a good name for any control. Do you mean InvoiceAmount? LoanAmount? etc.. In your subform, you call the payments made AmountDue. InvoiceID Payment ID Date Due Amount Due Balance Due 1000 001 01 March £500.00 £1000.00 1000 002 01 April £600.00 £ 400.00 That's really not correct. That should be PaymentAmt, or AmountPaid, etc.. You can have an AmountDue on a loan, but it's not the same as an the amount actually paid each month. I'll try again with the names you have at this time... =Forms!fmInvoice!Text24 - DSum("[AmountDue]","tbPaymentTerms", "InvoiceID = " & InvoiceID & " And PaymentID = " & PaymentID) -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Hello John, Thanks for helping I re-entered your formula and whilst that does produce a reducing balance on the first record (Invoice#1) the total payments are carried forward to Invoice#2 I m trying to achieve the followin Main Form [fmInvoice] Value is in [Text24]. Link Field is [InvoiceID] Subform=[fmPaymentTerms] Fields InvoiceID], [PaymentID], [AmountDue],[DateDue],[BalanceDue] The Subform should show the schedule of paymnets with a reducing balance. Example assuming Text24 Value = £1500.00 Subform- InvoiceID Payment ID Date Due Amount Due Balance Due 1000 001 01 March £500.00 £1000.00 1000 002 01 April £600.00 £ 400.00 1000 003 01 May £400.00 £ 0.00 Previous attempts hjave resulted in data in Record (invoice#1) spilling into subsequent Records. Brian "John W. Vinson" wrote: On Fri, 12 Mar 2010 14:20:05 -0800, Brian wrote: My apologies, I created a PaymentID as suggested. The full code in the [Balance] field is as follows - =Forms!fmInvoice!Text24-DSum("[AmountDue]","tbPaymentTerms","PaymentID = " & [PaymentID] & " and PaymentID = " & [PaymentID]) You're mistaking the meaning of the Boolean Algebra operator AND. It looks like the English language conjunction, but it isn't! AND is an operator in Boolean (true/false) logic, just like + is an operator in arithmatic. A AND B is TRUE if both A and B are TRUE; it is FALSE if either or both are false. So the statement PaymentID = 2 AND PaymentID = 2 will be TRUE if PaymentID is 2; it will be FALSE for any other value. If you want to see the sum of the amount due for all values of paymentID up to and including the one in the query, just use "PaymentID = " & [PaymentID] If not, please explain which payment ID's you want to sum. -- John W. Vinson [MVP] . |
#9
|
|||
|
|||
Reducing Balance on Invoice
Al,
I have been a pain haven't I? Your formula was correct and working fine. I tried to abbreviate the true field Names and correct them after pasting into my databse. I thought I was trying to make things simpler When requesting help in th future will I bear in mind your comments on protocols.. I am tryingt to learn Access just from the help files which don't always give suffiecient explanation or details. Your help (and patience) were invaluable. Brian "Al Campagna" wrote: Brian, When using code suggested by any respondent, you need to convert the example object names to your own. Form names, control names.. etc... You wrote on your 3/12 response... =Forms!fmInvoice!Text24-DSum("[AmountDue]","tbPaymentTerms","PaymentID = " & [PaymentID] & " and PaymentID = " & [PaymentID]) That's not what I suggested... ***My DSum involved two logical requirements. 1. That only payment amounts associated with that particular Loan (InvoiceID) be summed. 2. Only Payments made "up to" the current record be included in the summing. PaymentID = PaymentID Your code incorrectly states the "up to" PaymentID logic, and doesn't include any logic at all for which Payment values are associated with a particular loan. (= InvoiceID) You wrote on your 3/13 response... I re-entered your formula and whilst that does produce a reducing balance... I'll repeat... when you have trouble with code always cut & paste that code in your reply. Right now, we don't know what code you currently have, and where you've made a mistake. I also question your naming convention. Text24 is not a good name for any control. Do you mean InvoiceAmount? LoanAmount? etc.. In your subform, you call the payments made AmountDue. InvoiceID Payment ID Date Due Amount Due Balance Due 1000 001 01 March £500.00 £1000.00 1000 002 01 April £600.00 £ 400.00 That's really not correct. That should be PaymentAmt, or AmountPaid, etc.. You can have an AmountDue on a loan, but it's not the same as an the amount actually paid each month. I'll try again with the names you have at this time... =Forms!fmInvoice!Text24 - DSum("[AmountDue]","tbPaymentTerms", "InvoiceID = " & InvoiceID & " And PaymentID = " & PaymentID) -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Hello John, Thanks for helping I re-entered your formula and whilst that does produce a reducing balance on the first record (Invoice#1) the total payments are carried forward to Invoice#2 I m trying to achieve the followin Main Form [fmInvoice] Value is in [Text24]. Link Field is [InvoiceID] Subform=[fmPaymentTerms] Fields InvoiceID], [PaymentID], [AmountDue],[DateDue],[BalanceDue] The Subform should show the schedule of paymnets with a reducing balance. Example assuming Text24 Value = £1500.00 Subform- InvoiceID Payment ID Date Due Amount Due Balance Due 1000 001 01 March £500.00 £1000.00 1000 002 01 April £600.00 £ 400.00 1000 003 01 May £400.00 £ 0.00 Previous attempts hjave resulted in data in Record (invoice#1) spilling into subsequent Records. Brian "John W. Vinson" wrote: On Fri, 12 Mar 2010 14:20:05 -0800, Brian wrote: My apologies, I created a PaymentID as suggested. The full code in the [Balance] field is as follows - =Forms!fmInvoice!Text24-DSum("[AmountDue]","tbPaymentTerms","PaymentID = " & [PaymentID] & " and PaymentID = " & [PaymentID]) You're mistaking the meaning of the Boolean Algebra operator AND. It looks like the English language conjunction, but it isn't! AND is an operator in Boolean (true/false) logic, just like + is an operator in arithmatic. A AND B is TRUE if both A and B are TRUE; it is FALSE if either or both are false. So the statement PaymentID = 2 AND PaymentID = 2 will be TRUE if PaymentID is 2; it will be FALSE for any other value. If you want to see the sum of the amount due for all values of paymentID up to and including the one in the query, just use "PaymentID = " & [PaymentID] If not, please explain which payment ID's you want to sum. -- John W. Vinson [MVP] . . |
#10
|
|||
|
|||
Reducing Balance on Invoice
Brian,
Good deal! I hope I didn't sound too "preachy," but there are a few basic guidlines about how to ask a question, and how to respond. No problem... And... use the newsgroups when you need to. Good luck... and hang in there with your Access. -- Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Al, I have been a pain haven't I? Your formula was correct and working fine. I tried to abbreviate the true field Names and correct them after pasting into my databse. I thought I was trying to make things simpler When requesting help in th future will I bear in mind your comments on protocols.. I am tryingt to learn Access just from the help files which don't always give suffiecient explanation or details. Your help (and patience) were invaluable. Brian "Al Campagna" wrote: Brian, When using code suggested by any respondent, you need to convert the example object names to your own. Form names, control names.. etc... You wrote on your 3/12 response... =Forms!fmInvoice!Text24-DSum("[AmountDue]","tbPaymentTerms","PaymentID = " & [PaymentID] & " and PaymentID = " & [PaymentID]) That's not what I suggested... ***My DSum involved two logical requirements. 1. That only payment amounts associated with that particular Loan (InvoiceID) be summed. 2. Only Payments made "up to" the current record be included in the summing. PaymentID = PaymentID Your code incorrectly states the "up to" PaymentID logic, and doesn't include any logic at all for which Payment values are associated with a particular loan. (= InvoiceID) You wrote on your 3/13 response... I re-entered your formula and whilst that does produce a reducing balance... I'll repeat... when you have trouble with code always cut & paste that code in your reply. Right now, we don't know what code you currently have, and where you've made a mistake. I also question your naming convention. Text24 is not a good name for any control. Do you mean InvoiceAmount? LoanAmount? etc.. In your subform, you call the payments made AmountDue. InvoiceID Payment ID Date Due Amount Due Balance Due 1000 001 01 March £500.00 £1000.00 1000 002 01 April £600.00 £ 400.00 That's really not correct. That should be PaymentAmt, or AmountPaid, etc.. You can have an AmountDue on a loan, but it's not the same as an the amount actually paid each month. I'll try again with the names you have at this time... =Forms!fmInvoice!Text24 - DSum("[AmountDue]","tbPaymentTerms", "InvoiceID = " & InvoiceID & " And PaymentID = " & PaymentID) -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Brian" wrote in message ... Hello John, Thanks for helping I re-entered your formula and whilst that does produce a reducing balance on the first record (Invoice#1) the total payments are carried forward to Invoice#2 I m trying to achieve the followin Main Form [fmInvoice] Value is in [Text24]. Link Field is [InvoiceID] Subform=[fmPaymentTerms] Fields InvoiceID], [PaymentID], [AmountDue],[DateDue],[BalanceDue] The Subform should show the schedule of paymnets with a reducing balance. Example assuming Text24 Value = £1500.00 Subform- InvoiceID Payment ID Date Due Amount Due Balance Due 1000 001 01 March £500.00 £1000.00 1000 002 01 April £600.00 £ 400.00 1000 003 01 May £400.00 £ 0.00 Previous attempts hjave resulted in data in Record (invoice#1) spilling into subsequent Records. Brian "John W. Vinson" wrote: On Fri, 12 Mar 2010 14:20:05 -0800, Brian wrote: My apologies, I created a PaymentID as suggested. The full code in the [Balance] field is as follows - =Forms!fmInvoice!Text24-DSum("[AmountDue]","tbPaymentTerms","PaymentID = " & [PaymentID] & " and PaymentID = " & [PaymentID]) You're mistaking the meaning of the Boolean Algebra operator AND. It looks like the English language conjunction, but it isn't! AND is an operator in Boolean (true/false) logic, just like + is an operator in arithmatic. A AND B is TRUE if both A and B are TRUE; it is FALSE if either or both are false. So the statement PaymentID = 2 AND PaymentID = 2 will be TRUE if PaymentID is 2; it will be FALSE for any other value. If you want to see the sum of the amount due for all values of paymentID up to and including the one in the query, just use "PaymentID = " & [PaymentID] If not, please explain which payment ID's you want to sum. -- John W. Vinson [MVP] . . |
Thread Tools | |
Display Modes | |
|
|