Problem with Currency Field

[betheball]

I have a table with a price field. The data type is currency. For reasons unbeknownst to me, many of the values now have 4 decimal spots. I have an ASP page whereby the site owner can change prices in the db. The owner completes a form whereby he enters the old price and then the new price. The form data is then sent to an ASP page where an Update Query is run. Here is the problem, because many fields have more than two decimal spots, when the owner tries to change 17.99 to 18.99 the update does not run because in the db, 17.99 is really 17.9942. Is there a query I can write to go through the table and convert all the values to only two decimal spots? I think this happened because of another update query I have that increases all prices by a percentage. I think when that query ran, it added decimal spots. Any thoughts of how I can rectify this?

 

[o1110010]

Could you use an expression to search for the decimal point and then count how many characters are after it (X)? Then do your preference of code that only keeps the original price value minus X characters?

 

[betheball]

I don't know, can I? What I would like to do is write an update query in Access that I can run and get rid of the excess decimals in the table. Can anyone help with such a query?

 

[o1110010]

I don't know, can I? What I would like to do is write an update query in Access that I can run and get rid of the excess decimals in the table. Can anyone help with such a query?

Silly me. Almost always I think of the longer way to do something, eventually coming to a more simple way.

In an Update Query's Update To field, put Round([price],2)

price being the fieldname of the included table.

 

[betheball]

Is it possible to truncate as opposed to Rounding?

 

[o1110010]

Is it possible to truncate as opposed to Rounding?

Yup! But with prices, do you really want to truncate? Your employer's profit is reduced by a teeny tiny amount if you cut off the thousandths and on decimal places.

=Trunc([price],2)