Query problems

[Doug_C]

Hello,

This is a question I asked yesterday:

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.

I placed =[LoanAmt]-[AmtOut] in the control source of the Available funds to
Disburse field which works fine on the form. However, the total does not show
in the table. So I tried to place the formula in a query under the criteria
for AmtFund (the name of the Available funds to Disburse field) and I am
getting no figures all three fields.

What am I doing wrong???

Thanks!!

 

[Jeff L]

Putting the formula in a text box is for display purposes only. If you
want to save it to the table, use some code. Something like
Me.FundsAvail = Me.[LoanAmt] - Me.[AmtOut] should work.

Another thing you could do is in your query, put the formula in a
field, not in the criteria. That would look like FundsAvail: [LoanAmt]
- [AmtOut]

One thing to consider: Is it necessary to save the Funds Available
since it is a calculated value anyway? Just a thought.

 

[Klatuu]

The first issue is that you should not be storing a calculated value in a
table. Doing so violates one of the basic database normalization rules.
Since you have the calculation as a control source for a control, that is all
you need. If there are other places in your application like reports or
queries that need to present the result of the calculation, do the same
calculation there.

One technique to ensure the calculation is always the same regardless of
where it is done is to put the calculation in a function in a standard module
and call the function whenever you need the value:

Public Function AvailableFundsToDisburse(sngLoanAmt As Single,
sngOutStanding As Single) as Single
AvailableFundsToDisburse = sngLoanAmt - sngOutStanding
End Function

The reason the value is not showing up in your table is because to do so,
the control has to be bound to the recordset field. It is not because you
are using the control source for the calculation. The alternative, although
not recommended (reread the above), is to move the calculation to the Default
Value property and bind the control to the recordset field. The difference
will be that the value will not be correct until both of the other fields
have data.

 

[Doug_C]

Hello,

I want to thank both of you guys for answering my question.

Correct, I realize storing the calculations in the table is a no,no so that
is why I was trying to figure out how to get in the report through a query. I
did put the formula in the Field rather than the Criteria of the query and
that was it. Just a bad rookie mistake on my part, I should have better.

I will however save the piece of code by Klatuu with the rest of my codes
and could probably use it go forward. Also, thank you for explaining the
reason why a value will not show in a table. I will save this with my other
information and pass it along to other who may have the same issue.

As always, thank you very much for all your help and being part of this
forum!!

The first issue is that you should not be storing a calculated value in a
table. Doing so violates one of the basic database normalization rules.
Since you have the calculation as a control source for a control, that is all
you need. If there are other places in your application like reports or
queries that need to present the result of the calculation, do the same
calculation there.

One technique to ensure the calculation is always the same regardless of
where it is done is to put the calculation in a function in a standard module
and call the function whenever you need the value:

Public Function AvailableFundsToDisburse(sngLoanAmt As Single,
sngOutStanding As Single) as Single
AvailableFundsToDisburse = sngLoanAmt - sngOutStanding
End Function

The reason the value is not showing up in your table is because to do so,
the control has to be bound to the recordset field. It is not because you
are using the control source for the calculation. The alternative, although
not recommended (reread the above), is to move the calculation to the Default
Value property and bind the control to the recordset field. The difference
will be that the value will not be correct until both of the other fields
have data.

Hello,

This is a question I asked yesterday:

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.

I placed =[LoanAmt]-[AmtOut] in the control source of the Available funds to
Disburse field which works fine on the form. However, the total does not show
in the table. So I tried to place the formula in a query under the criteria
for AmtFund (the name of the Available funds to Disburse field) and I am
getting no figures all three fields.

What am I doing wrong???

Thanks!!