calculating a value in a text box

[Bob Wickham]

Hi,
This sounds really easy, but ........

I have a form/sub-form.
The Record Source of the sub-form is
SELECT tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, tblCommission.GST, tblCommission.DateBanked
FROM tblCommission;

I want the value in the GST text box to be calculated and shown as soon
as the user enters a value in the Payment text box.
GST is a tax and is always 10%.
So, if $10.00 is entered into Payment, the value $1.00 will be
immediately entered into GST but this should be able to be over-ruled by
the user.

Do I alter the Record Source with a WHERE GST = Payment * 0.10 type of
thing or is it a case of some code in an Event.

Before I spend many more hours on this can anyone offer some advice as
I fear I am going about this very wrong.

Thanks.

Bob

 

[Allen Browne]

Bob, tblCommission should have a field to store the GST rate, not the GST
amount.

The GST *amount* is dependent on other factors (the amount of the payment,
and whether the client is exempt at the time of the transaction.) One of the
basic rules of data normalization is that you do not store dependent data.

Instead, store the GST Rate at the time of the transaction. It will probably
be 0.1 for nearly every transaction, but you can set it to 0 if you have an
exempt client (e.g. exporting), or to a different rate for future
transactions when Mr Howard breaks his promise.

In a *query*, you then type this into a fresh column in the Field row:
GstAmount: [Payment] * [GstRate]
or better still:
GstAmount: CCur(Nz(Round([Payment] * [GstRate],2),0))

You now have the amount to show anywhere you want, e.g. by using the query
as the RecordSource of a form or report. The amount is never wrong, and it
all works without any code needed.

(Based on your example, I did assume that the Payment was Ex-GST. If it was
inc-GST, we should have been subtracting one-eleventh instead of adding
one-tenth.)

 

[Bob Wickham]

Bob, tblCommission should have a field to store the GST rate, not the GST
amount.

The GST *amount* is dependent on other factors (the amount of the payment,
and whether the client is exempt at the time of the transaction.) One of the
basic rules of data normalization is that you do not store dependent data.

Instead, store the GST Rate at the time of the transaction. It will probably
be 0.1 for nearly every transaction, but you can set it to 0 if you have an
exempt client (e.g. exporting), or to a different rate for future
transactions when Mr Howard breaks his promise.

In a *query*, you then type this into a fresh column in the Field row:
GstAmount: [Payment] * [GstRate]
or better still:
GstAmount: CCur(Nz(Round([Payment] * [GstRate],2),0))

You now have the amount to show anywhere you want, e.g. by using the query
as the RecordSource of a form or report. The amount is never wrong, and it
all works without any code needed.

(Based on your example, I did assume that the Payment was Ex-GST. If it was
inc-GST, we should have been subtracting one-eleventh instead of adding
one-tenth.)

Hi Allen,
I have added a field in tblCommission called GSTrate and change the
record source of the form to:
SELECT tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, CCur(Nz(Round([Payment]*[GSTrate],2),0)) AS GST,
tblCommission.DateBanked
FROM tblCommission;

You say to "store the GST Rate at the time of the transaction".
If I do this by entering 0.1 as that fields Default Value the
calculation on my form works but I can't change it for when clients are
"Not registered for GST" for instance.
Is there any way to over-ride this or do you think I should have a small
table with the GST rates in it that tblCommission will look up.

Bob

 

[Bob Wickham]

Bob, tblCommission should have a field to store the GST rate, not the
GST amount.

The GST *amount* is dependent on other factors (the amount of the
payment, and whether the client is exempt at the time of the
transaction.) One of the basic rules of data normalization is that you
do not store dependent data.

Instead, store the GST Rate at the time of the transaction. It will
probably be 0.1 for nearly every transaction, but you can set it to 0
if you have an exempt client (e.g. exporting), or to a different rate
for future transactions when Mr Howard breaks his promise.

In a *query*, you then type this into a fresh column in the Field row:
GstAmount: [Payment] * [GstRate]
or better still:
GstAmount: CCur(Nz(Round([Payment] * [GstRate],2),0))

You now have the amount to show anywhere you want, e.g. by using the
query as the RecordSource of a form or report. The amount is never
wrong, and it all works without any code needed.

(Based on your example, I did assume that the Payment was Ex-GST. If
it was inc-GST, we should have been subtracting one-eleventh instead
of adding one-tenth.)

Hi Allen,
I have added a field in tblCommission called GSTrate and change the
record source of the form to:
SELECT tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, CCur(Nz(Round([Payment]*[GSTrate],2),0)) AS GST,
tblCommission.DateBanked
FROM tblCommission;

You say to "store the GST Rate at the time of the transaction".
If I do this by entering 0.1 as that fields Default Value the
calculation on my form works but I can't change it for when clients are
"Not registered for GST" for instance.
Is there any way to over-ride this or do you think I should have a small
table with the GST rates in it that tblCommission will look up.

Bob

Hi again,

I solved it by changing the sql to:

SELECT tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, tblCommission.GSTrate,
CCur(Nz(Round([Payment]*[GSTrate],2),0)) AS GST, tblCommission.DateBanked
FROM tblCommission;

which gives me a text box to alter the rate of GST if necessary but it
defaults to 0.1

I think this is maybe what you meant. In any case, it works, but please
let me know if theres a better way.

Have an fun Australia Day.

Thanks again

Bob Wickham

 

[Bob Wickham]

Bob, tblCommission should have a field to store the GST rate, not the
GST amount.

The GST *amount* is dependent on other factors (the amount of the
payment, and whether the client is exempt at the time of the
transaction.) One of the basic rules of data normalization is that you
do not store dependent data.

Instead, store the GST Rate at the time of the transaction. It will
probably be 0.1 for nearly every transaction, but you can set it to 0
if you have an exempt client (e.g. exporting), or to a different rate
for future transactions when Mr Howard breaks his promise.

In a *query*, you then type this into a fresh column in the Field row:
GstAmount: [Payment] * [GstRate]
or better still:
GstAmount: CCur(Nz(Round([Payment] * [GstRate],2),0))

You now have the amount to show anywhere you want, e.g. by using the
query as the RecordSource of a form or report. The amount is never
wrong, and it all works without any code needed.

(Based on your example, I did assume that the Payment was Ex-GST. If
it was inc-GST, we should have been subtracting one-eleventh instead
of adding one-tenth.)

Hi Allen,
I have added a field in tblCommission called GSTrate and change the
record source of the form to:
SELECT tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, CCur(Nz(Round([Payment]*[GSTrate],2),0)) AS GST,
tblCommission.DateBanked
FROM tblCommission;

You say to "store the GST Rate at the time of the transaction".
If I do this by entering 0.1 as that fields Default Value the
calculation on my form works but I can't change it for when clients are
"Not registered for GST" for instance.
Is there any way to over-ride this or do you think I should have a small
table with the GST rates in it that tblCommission will look up.

Bob

Hi again,

I solved it by changing the sql to:

SELECT tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, tblCommission.GSTrate,
CCur(Nz(Round([Payment]*[GSTrate],2),0)) AS GST, tblCommission.DateBanked
FROM tblCommission;

which gives me a text box to alter the rate of GST if necessary but it
defaults to 0.1

I think this is maybe what you meant. In any case, it works, but please
let me know if theres a better way.

Have a fun Australia Day.

Thanks again

Bob Wickham

 

[Allen Browne]

Yes, you will have a text box where you can change the GstRate to 0 if the
person does not have to pay/charge GST.

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

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

Bob, tblCommission should have a field to store the GST rate, not the
GST amount.

The GST *amount* is dependent on other factors (the amount of the
payment, and whether the client is exempt at the time of the
transaction.) One of the basic rules of data normalization is that you
do not store dependent data.

Instead, store the GST Rate at the time of the transaction. It will
probably be 0.1 for nearly every transaction, but you can set it to 0 if
you have an exempt client (e.g. exporting), or to a different rate for
future transactions when Mr Howard breaks his promise.

In a *query*, you then type this into a fresh column in the Field row:
GstAmount: [Payment] * [GstRate]
or better still:
GstAmount: CCur(Nz(Round([Payment] * [GstRate],2),0))

You now have the amount to show anywhere you want, e.g. by using the
query as the RecordSource of a form or report. The amount is never
wrong, and it all works without any code needed.

(Based on your example, I did assume that the Payment was Ex-GST. If it
was inc-GST, we should have been subtracting one-eleventh instead of
adding one-tenth.)

Hi Allen,
I have added a field in tblCommission called GSTrate and change the
record source of the form to:
SELECT tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, CCur(Nz(Round([Payment]*[GSTrate],2),0)) AS GST,
tblCommission.DateBanked
FROM tblCommission;

You say to "store the GST Rate at the time of the transaction".
If I do this by entering 0.1 as that fields Default Value the calculation
on my form works but I can't change it for when clients are "Not
registered for GST" for instance.
Is there any way to over-ride this or do you think I should have a small
table with the GST rates in it that tblCommission will look up.

Bob

Hi again,

I solved it by changing the sql to:

SELECT tblCommission.LoanNo, tblCommission.PaymentDate,
tblCommission.Payment, tblCommission.GSTrate,
CCur(Nz(Round([Payment]*[GSTrate],2),0)) AS GST, tblCommission.DateBanked
FROM tblCommission;

which gives me a text box to alter the rate of GST if necessary but it
defaults to 0.1

I think this is maybe what you meant. In any case, it works, but please
let me know if theres a better way.

Have a fun Australia Day.

Thanks again

Bob Wickham