How do i assign a calculated value to a table in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a table containing the fields 'quote before VAT', 'VAT' and
'quote after VAT'. I have then created a form based on these three fields.

Basically the idea is that when the user enters the quote before VAT, my VAT
field will automatically workout 17.5 % of the value, and display it in the
field.

I have managed to produce an expression that will work out 17.5%, however it
will only work when the formula is placed in the 'control source' of the
properties window, and therefore loses the link to the table.

How can i apply this formula whilst keeping the control source linked to the
table??

Any help is greatly appreciated!
Michael Jacques
 
michael_jacques said:
I have created a table containing the fields 'quote before VAT',
'VAT' and 'quote after VAT'. I have then created a form based on
these three fields.

Basically the idea is that when the user enters the quote before VAT,
my VAT field will automatically workout 17.5 % of the value, and
display it in the field.

I have managed to produce an expression that will work out 17.5%,
however it will only work when the formula is placed in the 'control
source' of the properties window, and therefore loses the link to the
table.

How can i apply this formula whilst keeping the control source linked
to the table??
Click to expand...

You don't. Calculated values should not be saved to the table but just computed
as needed. Just remove the field from your table. If you're thinking you
*need* it in the table for some reason you are incorrect as it can be calculated
in any form, report, or query, and the last can be substituted any place you
would normally have used the table directly.
 
Michael, the calculated VAT should not be stored in a table.
It should be a calculcated field in a query.
That way it can never be wrong.

In query design view, type this into a fresh column of the Field row:
VAT: CCur(Round(Nz([Amount],0) * 0.175, 2))
where "Amount" represents the name of your existing field. You never have to
worry about whether the value is correct, as you do when it is stored.

If you are worried that one day the government will change the VAT rate, you
could store the 17.5% on every row as a Number field of size Double, and
format Percent.

For more info, 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.

message
news:[email protected]...
 
Rick Brandt said:
You don't. Calculated values should not be saved to the table but just computed
as needed. Just remove the field from your table. If you're thinking you
*need* it in the table for some reason you are incorrect as it can be calculated
in any form, report, or query, and the last can be substituted any place you
would normally have used the table directly.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Thanks for your reply, that has really helped!!
Click to expand...
 
Thanks for your help, i found it very useful!!!

Allen Browne said:
Michael, the calculated VAT should not be stored in a table.
It should be a calculcated field in a query.
That way it can never be wrong.

In query design view, type this into a fresh column of the Field row:
VAT: CCur(Round(Nz([Amount],0) * 0.175, 2))
where "Amount" represents the name of your existing field. You never have to
worry about whether the value is correct, as you do when it is stored.

If you are worried that one day the government will change the VAT rate, you
could store the 17.5% on every row as a Number field of size Double, and
format Percent.

For more info, 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.

message
I have created a table containing the fields 'quote before VAT', 'VAT' and
'quote after VAT'. I have then created a form based on these three
fields.

Basically the idea is that when the user enters the quote before VAT, my
VAT
field will automatically workout 17.5 % of the value, and display it in
the
field.

I have managed to produce an expression that will work out 17.5%, however
it
will only work when the formula is placed in the 'control source' of the
properties window, and therefore loses the link to the table.

How can i apply this formula whilst keeping the control source linked to
the
table??

Any help is greatly appreciated!
Michael Jacques
Click to expand...
Click to expand...
 
I agree with everything the other posts say about calculated field.
But to answer your question, using the name of the control, you can
do this in the After Update event of that control.

Me.frm_Vat=Me.frm_BeforeVat * 1.175

You can also do stuff like this:

If Me.Quantity > 123 then
Me.Big_Customer_Flag = True
else
Me.Big_Customer_Flag = False
Endif

But, remember, all of the above can be done in a query and doesn't
require you to store it in a table. The problem with storing
calculated values is if the quantity field or Before VAT is changed
by a user who is in datasheet view of the table and then runs a report,
the answer for the Big_Customer_Flag or in you case, the VAT, will be
incorrect.


Ron
 
Back
Top