Reducing Balance on Invoice

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

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
 
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."
 
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
Click to expand...
Any thoughts on a tweak?
Brian

Al Campagna said:
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 said:
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
Click to expand...


.
Click to expand...
 
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 said:
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
Click to expand...
Any thoughts on a tweak?
Brian

Al Campagna said:
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 said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Any thoughts on a tweak?
Brian

Al Campagna said:
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."

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




.
Click to expand...
Click to expand...


.
Click to expand...
 
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])
Click to expand...

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.
 
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 said:
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])
Click to expand...

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.
Click to expand...
 
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])
Click to expand...
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...
Click to expand...
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
Click to expand...
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 said:
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 said:
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])
Click to expand...

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.
Click to expand...
Click to expand...
 
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 said:
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])
Click to expand...
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...
Click to expand...
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
Click to expand...
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 said:
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 said:
On Fri, 12 Mar 2010 14:20:05 -0800, Brian
<[email protected]>
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.
Click to expand...
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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])
Click to expand...
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...
Click to expand...
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
Click to expand...
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 said:
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


:

On Fri, 12 Mar 2010 14:20:05 -0800, Brian
<[email protected]>
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.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top