Difference between two fields

[Doug_C]

Hello,

I have a question that I am sure you experts can help with.

I have a form with three fields; "Loan Amount", "Amount Outstanding" and
"Available Funds to Disburse". What I need to do is this:

If the Loan amount fields shows $500,000 and Amount outstanding is $100,000
I need for the Available funds to Disburse field to show the difference which
$400,000. If possible, please simplify answer by letting me what formula is
and where in the avaialble funds to disburse field it should be placed.

As always folks, thank you for all your time and help!!!

 

[Allen Browne]

Your table should store only 2 of those 3 fields.

Say you choose to store Loan and Outstanding as fields in your table.
Create a query, and type this into the Field row:
Available: [Loan] - [Outstanding]
Save the query.
All done.

You can use the query in your form or report, and be absolutely certain that
Access will keep the Available amount right for you.

If you want more information, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Doug_C]

Hi Allen,

Thank you for your quick response!! I believe this will work for what I need
it for. May I ask a novice question?

I know how to get this to work in a report (using the query), but how do I
use the query to show the "Available" field results on the form? I'm sure I
ever did this. Sorry to sound dense but this is how I learn. I save all my
response in a folder and review this board every so often to pick up some
code that might come in handy down the road. Also, I have your site saved
which has been a big help as well.

Thanks!

Your table should store only 2 of those 3 fields.

Say you choose to store Loan and Outstanding as fields in your table.
Create a query, and type this into the Field row:
Available: [Loan] - [Outstanding]
Save the query.
All done.

You can use the query in your form or report, and be absolutely certain that
Access will keep the Available amount right for you.

If you want more information, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a question that I am sure you experts can help with.

I have a form with three fields; "Loan Amount", "Amount Outstanding" and
"Available Funds to Disburse". What I need to do is this:

If the Loan amount fields shows $500,000 and Amount outstanding is
$100,000
I need for the Available funds to Disburse field to show the difference
which
$400,000. If possible, please simplify answer by letting me what formula
is
and where in the avaialble funds to disburse field it should be placed.

As always folks, thank you for all your time and help!!!

 

[enginerd477]

Doug:

I read your post and decided to try it out to see what i could get.
create your form with the three fields. change the control source of
your funds available (by clicking on the box to the right of the dialog
box) to the following:

=[loan amount]-[amount outstanding]
and I got the value to show.

 

[Doug_C]

Hi enginerd477,

Thank you for answering my question. Yes, that also sounds like a good ideas
and I think I will try that and see how it works with my table. Thank you
again for your help!! Have a great day!!!!

Doug:

I read your post and decided to try it out to see what i could get.
create your form with the three fields. change the control source of
your funds available (by clicking on the box to the right of the dialog
box) to the following:

=[loan amount]-[amount outstanding]
and I got the value to show.

I know how to get this to work in a report (using the query), but how do I
use the query to show the "Available" field results on the form? I'm sure I
ever did this.

 

[Allen Browne]

The calculated text box is fine.

Or, you can set the RecordSource property of your form to the query that has
the calculated field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi enginerd477,

Thank you for answering my question. Yes, that also sounds like a good
ideas
and I think I will try that and see how it works with my table. Thank you
again for your help!! Have a great day!!!!

Doug:

I read your post and decided to try it out to see what i could get.
create your form with the three fields. change the control source of
your funds available (by clicking on the box to the right of the dialog
box) to the following:

=[loan amount]-[amount outstanding]
and I got the value to show.

I know how to get this to work in a report (using the query), but how
do I
use the query to show the "Available" field results on the form? I'm
sure I
ever did this.

 

[enginerd477]

I forgot to mention this before but i had each of the field set to
currency with 2 decimal places of default. i don't know if that will
make much of a difference but thats something else to consider.