Access not rounding up when it should

[xyle]

i have a simple formula that goes like this

curPenalty = Round(txtGross * 0.05, 2)

curPenalty is a variable set to currency and txtGross is a field set to currency with an auto decimal place.

When txtGross value was $261.70 * 0.05 would equal $13.085 rounding should make it equal $13.09 but it comes out as $13.08

How do i get it round the third decimal place up if it is greater than equal to 5 or down if less than 5?

 

[Remou]

Use format. Round does not use .5 to round up and that is statistically accurate:

http://msdn.microsoft.com/library/d...html/c25665d8-cea5-4dbc-a320-c2c9722184bc.asp

 

[PHV]

Round use a financial algorithm.
You may try the CLng function:
curPenalty = CLng(100 * txtGross * 0.05) / 100

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

PHV, please say why you do not recommend format, thanks.

 

[PHV]

Just because Format returns a string instead of a (wanted) numeric value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Ok. Thanks again.

 

[xyle]

It still rounds down with PHV answer. I neet it to round up.

 

[xyle]

Sorry the format function worked for me.

 

[Remou]

Try Format

Format(txtGross,"#,##0.00")

You can add Val, if necessary.

 

[missinglinq]

Here's a function to round up. You give it the number and the number of digits to round to. In a standard module place this code:

Public Function RoundTotal(ByVal dblNumber As Double, ByVal intDecimals As Integer) As Double

    Dim dblFactor As Double
    Dim dblTemp As Double    

    dblFactor = 10 ^ intDecimals
    dblTemp = dblNumber * dblFactor + 0.5
    RoundTotal = Int("" & dblTemp) / dblFactor
  
End Function

Then, in your code, where RN is the R(ounded) N(umber) you want

RN=RoundTotal(YourNumber,NumberOfDecimalsToRoundTo)

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Remou]

This all seems like a lot of work. Format works very nicely for rounding up, but no one in this thread seems to like it.

 

[missinglinq]

Indeed it does, Remou; I've added it to my arsernal now! When looking at a post, I usually assume that a posted solution has been tried and failed if the OP continues looking for an answer, but I know that's not always true! I've posted compact, working solutions dozens of times, only to see the OP and others go on for days and says trying to "solve" the problem!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!