Round 4 decimals up

[Guest]

Hello, Using MS 2002. I know this is spelled out below but - need a liitle
help. I have records that have a cost field out to 4 decimals (12.034). I
would like to set up an update qry to round up to the nearest dollar & cents
$12.04. Any help or simple rond function to do this?
Thanks, Ed

 

[Marshall Barton]

Hello, Using MS 2002. I know this is spelled out below but - need a liitle
help. I have records that have a cost field out to 4 decimals (12.034). I
would like to set up an update qry to round up to the nearest dollar & cents
$12.04. Any help or simple rond function to do this?

UPDATE table SET Cost = Round(Cost, 2)

Always make a backup of the table before doing this kind of
global update.

 

[Jeff Boyce]

Ed

Do you mean you want to STORE no more than two decimal places, or that you
want to SEE no more than two?

You could set the Decimal Places property to 2, even though more were
stored...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[John Spencer]

SInce you want to round up, I would try the following.

Public Function fRoundUp (dblNumber As Double, _
Optional intPlaces As Integer) As Double
fRoundUp= -Int(-dblNumber * 10 ^ intPlaces) / 10 ^ intPlaces
End Function

This works with positive numbers. Also it will error with non-numeric
values (null, strings that can't be interpreted as a date) Also, since you
are working with doubles (floating point) the rounding can get a little
strange if you round up to more digits then you pass in.
For example
froundup (2.0211,6) --> 2.021101

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jamie Collins]

Do you mean you want to STORE no more than twodecimalplaces, or that you
want to SEE no more than two?

You could set theDecimalPlaces property to 2, even though more were
stored...

The OP could use the DECIMAL type with the Scale property set to 2 to
ensure no more than 2 decimal places are stored but in practice it is
better to store an extra decimal place to make custom rounding
possible e.g. the OP's use of the ROUND() function suggests banker's
rounding is desired whereas a DECIMAL(n, 2) column will exhibit
symmetric truncation.

Jamie.

--