Rounding Currency by update query

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

Hi all: Using xp pro, access 2002.
Table has price field set to currancy and displays 2 digits but when clicked
in extends to actual 4 decimal number as imported, I wwould like to round up
this field by update query so all calculations based on this field will only
use the new rounded up 2 decimal number.
Thanks, Ed
 
Try an Update Query something like:

UPDATE YourTable
SET YourField = CCur(YourField)

HTH
Van T. Dinh
MVP (Access)
 
Thanks, Do you mean to insert into update to: SET [price]=CCur[price]?
The field data type in table is currancy but has 4 decimals when entered.
I used the append data query to send this data into the currency field from
a number field type.
 
I am not sure whether I understood your question???

If you meant you used an Append Query to add Records from
the source with numbers having 4 decimal places into your
Field of Currency type in the destination Table then the
values of your Currency will have 4 decimal places. Note
that in Access, Currency type *stores 4 decimal places by
default*. This is done for extra accuracy and you simply
display 2 decimal places as per normal.

If you actually want to store the values accurate to 2
decimal places(actually Access still store 4 decimal
places but the last 2 digits are 00), you need to use Round
() function in your Append Query to round the number to 2
decimal places before inserted into the destination.

Check Access VB Help on the Round() function.

HTH
Van T. Dinh
MVP (Access)
 
Exactly, Thank you. I put Round([price]2) into the update to section of the
[price] column and it worked just fine (changed 1.139 to 1.1400). Thanks,
again for the info on currancy set to 4 decimals and displaying 2 but jumps
to 4 when entered, also calculates based on 4 decimals.
Van T. Dinh said:
I am not sure whether I understood your question???

If you meant you used an Append Query to add Records from
the source with numbers having 4 decimal places into your
Field of Currency type in the destination Table then the
values of your Currency will have 4 decimal places. Note
that in Access, Currency type *stores 4 decimal places by
default*. This is done for extra accuracy and you simply
display 2 decimal places as per normal.

If you actually want to store the values accurate to 2
decimal places(actually Access still store 4 decimal
places but the last 2 digits are 00), you need to use Round
() function in your Append Query to round the number to 2
decimal places before inserted into the destination.

Check Access VB Help on the Round() function.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Thanks, Do you mean to insert into update to: SET [price] =CCur[price]?
The field data type in table is currancy but has 4 decimals when entered.
I used the append data query to send this data into the currency field from
a number field type.
Click to expand...
Click to expand...
 
Back
Top