Calculating Question

[James T.]

Access 2000

I have a form (LoanPayment,)that has a table that I
dropped in as a subform (LoanHistory). As payments are
posted each month I would like to have the user input the
payment amount (PaymentAmount)and the next field
(AmountRI) be a calculated field like this:

Previous (AmountRI)-(PaymentAmount)= New(AmountRI)
without it causing a loop. I tried =sum([AmountRI]-
[PaymentAmount]) and it gives me a negative value the same
as the PaymentAmount.

Any help appreciated.

James

 

[tina]

you might try putting the calculated control in the subform's footer
section, with the calculation something like:

=[TotalAmountOfLoan]-Sum([Payment])

if you're looking for a running sum for each payment record, you could try
something more like:

=[TotalAmountOfLoan] - DSum("Payment","PaymentsTable","PaymentDate < #" &
[Forms]![MyOpenForm]![NameOfSubformControl].Form![PaymentDateField] & "#")
and of course you have to also filter for the payment records that "belong
to" the partcular loan record you're looking it.

both of the above are "air code", so suggest you just use either/both as
ideas for how to write a workable solution.

hth

 

[James T.]

Tina,

Looking for a simple solution (for my simple mind ) I
tried a sum query in the form footer that totals the sum
of payments made. I then put an unbound box in the footer
and put in a refrence to the original amount, which is in
one table, LoanPaymentTBL and tried to subtract the total
which shows in the query, TotalPayments.

I tried the Expression

=sum([LoanPaymentTBL!TotalLoan]-[PaymentSum!TotalPayments])

But I get no results. Nada.

This won't give me a running total, but should give me a
current balance. Seems simple, but I am missing something
I'm sure. Are my refrences and syntax correct?

Thanks,
James

-----Original Message-----
you might try putting the calculated control in the subform's footer
section, with the calculation something like:

=[TotalAmountOfLoan]-Sum([Payment])

if you're looking for a running sum for each payment record, you could try
something more like:

=[TotalAmountOfLoan] - DSum

("Payment","PaymentsTable","PaymentDate < #" &

[Forms]![MyOpenForm]![NameOfSubformControl].Form! [PaymentDateField] & "#")
and of course you have to also filter for the payment records that "belong
to" the partcular loan record you're looking it.

both of the above are "air code", so suggest you just use either/both as
ideas for how to write a workable solution.

hth

Access 2000

I have a form (LoanPayment,)that has a table that I
dropped in as a subform (LoanHistory). As payments are
posted each month I would like to have the user input the
payment amount (PaymentAmount)and the next field
(AmountRI) be a calculated field like this:

Previous (AmountRI)-(PaymentAmount)= New(AmountRI)
without it causing a loop. I tried =sum([AmountRI]-
[PaymentAmount]) and it gives me a negative value the same
as the PaymentAmount.

Any help appreciated.

James

.

 

[tina]

we could do a back and forth Q&A all day long, to help me figure out exactly
how you've set this up so i can tell where the problem is - but i just don't
have the time, sorry.
if you want to send me a copy of your db (with proprietary data deleted and
a few "dummy" records entered), i'll take a look, try to set up a simple
solution and return it to you for your review.
compact the copy and zip to under 1 MB, then attach to an email that
references the newsgroups in the subject line, and email to
ttaccKILLSPAMess1 at DEADyahoo dot com - removing only the capital letters
from the address.

Tina,

Looking for a simple solution (for my simple mind ) I
tried a sum query in the form footer that totals the sum
of payments made. I then put an unbound box in the footer
and put in a refrence to the original amount, which is in
one table, LoanPaymentTBL and tried to subtract the total
which shows in the query, TotalPayments.

I tried the Expression

=sum([LoanPaymentTBL!TotalLoan]-[PaymentSum!TotalPayments])

But I get no results. Nada.

This won't give me a running total, but should give me a
current balance. Seems simple, but I am missing something
I'm sure. Are my refrences and syntax correct?

Thanks,
James

-----Original Message-----
you might try putting the calculated control in the subform's footer
section, with the calculation something like:

=[TotalAmountOfLoan]-Sum([Payment])

if you're looking for a running sum for each payment record, you could try
something more like:

=[TotalAmountOfLoan] - DSum

("Payment","PaymentsTable","PaymentDate < #" &

[Forms]![MyOpenForm]![NameOfSubformControl].Form! [PaymentDateField] & "#")
and of course you have to also filter for the payment records that "belong
to" the partcular loan record you're looking it.

both of the above are "air code", so suggest you just use either/both as
ideas for how to write a workable solution.

hth

Access 2000

I have a form (LoanPayment,)that has a table that I
dropped in as a subform (LoanHistory). As payments are
posted each month I would like to have the user input the
payment amount (PaymentAmount)and the next field
(AmountRI) be a calculated field like this:

Previous (AmountRI)-(PaymentAmount)= New(AmountRI)
without it causing a loop. I tried =sum([AmountRI]-
[PaymentAmount]) and it gives me a negative value the same
as the PaymentAmount.

Any help appreciated.

James

.

 

[James T.]

Done. And Thank YOU!

James

-----Original Message-----
we could do a back and forth Q&A all day long, to help me figure out exactly
how you've set this up so i can tell where the problem is - but i just don't
have the time, sorry.
if you want to send me a copy of your db (with proprietary data deleted and
a few "dummy" records entered), i'll take a look, try to set up a simple
solution and return it to you for your review.
compact the copy and zip to under 1 MB, then attach to an email that
references the newsgroups in the subject line, and email to
ttaccKILLSPAMess1 at DEADyahoo dot com - removing only the capital letters
from the address.

Tina,

Looking for a simple solution (for my simple mind ) I
tried a sum query in the form footer that totals the sum
of payments made. I then put an unbound box in the footer
and put in a refrence to the original amount, which is in
one table, LoanPaymentTBL and tried to subtract the total
which shows in the query, TotalPayments.

I tried the Expression

=sum([LoanPaymentTBL!TotalLoan]-[PaymentSum! TotalPayments])

But I get no results. Nada.

This won't give me a running total, but should give me a
current balance. Seems simple, but I am missing something
I'm sure. Are my refrences and syntax correct?

Thanks,
James

-----Original Message-----
you might try putting the calculated control in the subform's footer
section, with the calculation something like:

=[TotalAmountOfLoan]-Sum([Payment])

if you're looking for a running sum for each payment record, you could try
something more like:

=[TotalAmountOfLoan] - DSum

("Payment","PaymentsTable","PaymentDate < #" &

[Forms]![MyOpenForm]![NameOfSubformControl].Form! [PaymentDateField] & "#")
and of course you have to also filter for the payment records that "belong
to" the partcular loan record you're looking it.

both of the above are "air code", so suggest you just

use
either/both as

ideas for how to write a workable solution.

hth


"James T." <[email protected]> wrote

in
message

Access 2000

I have a form (LoanPayment,)that has a table that I
dropped in as a subform (LoanHistory). As payments are
posted each month I would like to have the user input the
payment amount (PaymentAmount)and the next field
(AmountRI) be a calculated field like this:

Previous (AmountRI)-(PaymentAmount)= New(AmountRI)
without it causing a loop. I tried =sum([AmountRI]-
[PaymentAmount]) and it gives me a negative value the same
as the PaymentAmount.

Any help appreciated.

James


.

.