You should pay close heed to what Turtle said about not
storing data that you can always calculate on the fly. There
are however some exceptions such as sales tax calculations
where the sales tax is prone to change over time. From your
expanded explanation, it is possible that this could fit
your model if your 1.35 is a markup and the markup could
change over time and you wouldn't want the historical values
to change. I think that you would have been bitten in Excel
here as well from your formula. If you do not NEED to store
it, reread my query suggestion again or explain how you are
getting the error better.
If you are trying to enter this calculation in the design
view of a table ( you weren't clear about the design view of
what ) this is not the correct place to do it if you are
trying to store the calculation in a field called Price that
is already created in the table. If the query contrived
field is not what you want to do and you do need to store
it, the best way in Access is to do it in VBA code at a
place that fires every time the Cost field has an entry or a
modification to an entry. This is the AfterUpdate event of
the Cost text box on your entry form. If this is what you
want to do, make sure that you have a Price field in the
table and on your entry form.
In design mode of your entry form, doubleclick on the Cost
field to bring up it's property box. Go to the Events tab
and find the AfterUpdate event. Click into that line and you
will see a little builder button on the right side of the
line. Click on that and choose 'Code' from the list. You
will now be popped into the VBA editor window. Now type in
the following line...
Me!Price = Me!Cost * 1.35
Close the code window and save the form and you should be
ready to go if these names are identical to yours.
Beware, that this is the quick and easy way. A good
programmer would build another table to hold the markup
amount and look that amount up in the calculation. That way,
if the markup changes you just need to change the amount in
the table. If you like, do this and call the table tblMarkup
and the field Markup and you could change the calculation to
be...
Me!Price = Me!Cost * DLookup("Markup","tblMarkup")
Regards,
--
Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Thanks Gary, but it is still not working.
Here is what I am trying to do ...
I am trying to multiply the values in a column (cost) by
1.35 and post result in another column (price)
Normally in Excel, I will put a formula in my price column
as: =d1*1.35. This works every time in Excel, but not in
Access.
When I try to do this in design view, I get an
error 'database engine does not recognize either the
field "cost" in a validation expression' and so on and so
forth.
Thanks
-----Original Message-----
You can easily do this by creating a new column in a query
or with a calculated control on either a form or a report.
For a query type something like the following into a new
column...
NewNumber: [YourColumnName] * 5
In a control...
=[YourColumnName] * 5
If you do it in a query once, you can always then bind your
controls to 'NewNumber' or whatever it ends up being.
--
Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
W.B Trader said:
Hello
I am trying to take the value in a column on a table in
Access and multiply it to a number and the result put in a
new column.
I can do this in Excel and then import it into Access
fine, but I would prefer to do this directly in Access if
that is possible.
Thanks
.
