calculating a value in a text box

[Bob Wickham]

Hi,
A few days ago, in this newsgroup, Allen Browne helped me with
calculating a value in a text box.
He also steered me straight about database design and that its not good
to store data that is dependent on other factors and can be calculated
when needed.
My problem arose when I needed to calculate the GST (tax) on a Payment
when entering information via a form. I wanted to transfer the resulting
calculation to my table, tblCommission.
Allen suggested I shouldn't do this as it was easy to calculate it each
time it was needed and it shouldn't be stored in a table.

I now have the following sql as the record source of my form.
SELECT tblCommission.LoanNo, tblCommission.CommissionType,
tblCommission.PaymentDate, tblCommission.Payment, tblCommission.GSTrate,
CCur(Nz(Round([Payment]*[GSTrate],2),0)) AS GST, tblCommission.DateBanked
FROM tblCommission;

I have since realised that I need to store the GST in the table because
only a very small amount of the data in tblCommission is entered via
this form. The rest comes from a 3rd party with the amount of GST
already calculated and I simply, append it to my table.

So, the problem I have now is, the appended data includes a value in the
GST column of my table and the above sql leaves it blank.
But I still need to be able to calculate it when data is entered via the
form.

I have considered a kind of reverse approach by using the GST amount in
the data that I append, to calculate the rate of GST that applied but
its very important that the $ amount in the database and the $ amount
actually in the bank balances to the last cent, and my testing has shown
that this may not be possible if I use this method.

Any ideas.

Bob Wickham

 

[Allen Browne]

Hi Bob

Take a look at:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

The 2nd part of the article explains how to use the AfterUpdate event of a
text box to write the calculated result to another control on the form, if
you want to do it that way.