Question Rounding

[catbeasy]

Using: Access 97

I looked in the help for rounding, but didn't see any single function that could provide rounding assistance. Excel has the round function, but don't see anything like that in Access..

Any suggestions?

Thanks..

 

[gemma-the-husky]

for proper rounding

cint
clng

or to round down
int
fix

 

[Rabbie]

There is a Round function in Access. See Access Help for more info.

Round(expression [, numdecimalplaces ] )

 

[gemma-the-husky]

of course, rabbie

there wasnt one in A97, so i forget about it being added later

 

[catbeasy]

for proper rounding

cint
clng

or to round down
int
fix

What about for rouding to x number of decimal places. So if have 3.567889 but want it rounded to the hundreth place (so it should read 3.57)..

Thanks..

 

[Rabbie]

What about for rouding to x number of decimal places. So if have 3.567889 but want it rounded to the hundreth place (so it should read 3.57)..

Thanks..

If you look at my post use Round

for your example

Round(3.567889, 2) gives 3.57

 

[boblarson]

If you look at my post use Round

for your example

Round(3.567889, 2) gives 3.57

Rabbie:

The OP said they were using Access 97 so the Round Function isn't in Access 97. They would need this instead:
http://www.mvps.org/access/modules/mdl0054.htm

 

[Rabbie]

Rabbie:

The OP said they were using Access 97 so the Round Function isn't in Access 97. They would need this instead:
http://www.mvps.org/access/modules/mdl0054.htm

Thanks Bob. I had forgotten it wasn't in A97 - so long since I used it.

 

[boblarson]

Thanks Bob. I had forgotten it wasn't in A97 - so long since I used it.

Me too, so I went on a "Google Quest" and found that to be the case But we have to also remember that Dave (gemma) had actually said that in this thread but it was stated in a way that could be easily overlooked.

 

[catbeasy]

sounds good, thanks for all ya'lls input..

 

[ByteMyzer]

You could simply use this function in MS Access 97:

[COLOR="Navy"]Public Function[/COLOR] Round( _
    [COLOR="navy"]ByVal[/COLOR] Number, _
    [COLOR="navy"]Optional ByVal[/COLOR] NumDigitsAfterDecimal [COLOR="navy"]As Long[/COLOR])

    [COLOR="Navy"]If[/COLOR] IsNumeric(Number) = [COLOR="navy"]True Then[/COLOR]
        Round = [COLOR="navy"]CDbl([/COLOR]Format(Number, "#." _
            & String(NumDigitsAfterDecimal, "#"))[COLOR="navy"])
    End If

End Function[/COLOR]

 

[boblarson]

You could simply use this function in MS Access 97:

[COLOR="Navy"]Public Function[/COLOR] Round( _
    [COLOR="navy"]ByVal[/COLOR] Number, _
    [COLOR="navy"]Optional ByVal[/COLOR] NumDigitsAfterDecimal [COLOR="navy"]As Long[/COLOR])

    [COLOR="Navy"]If[/COLOR] IsNumeric(Number) = [COLOR="navy"]True Then[/COLOR]
        Round = [COLOR="navy"]CDbl([/COLOR]Format(Number, "#." _
            & String(NumDigitsAfterDecimal, "#"))[COLOR="navy"])
    End If

End Function[/COLOR]

Wouldn't that function be subject to the statement on the link I provided earlier:

The primary reason behind such errors is that floating-point calculations with Single or Double data type often can't be represented in binary (due to large range of numbers these data types can operate on), leading to rounding inaccuracies.

 

[ByteMyzer]

Wouldn't that function be subject to the statement on the link I provided earlier:

You tell me:

Number           Digits Round (A2K+)     Round (Ken Getz) Round (ByteMyzer)
---------------------------------------------------------------------------
439437210.559845   5    439437210.55984  439437210.55985  439437210.55985
491863787.174225   5    491863787.17422  491863787.17423  491863787.17423
422886192.798615   5    422886192.79861  422886192.79861  422886192.79862

 

[catbeasy]

This problem has been solved! ByteMyzer's formula worked well..

 

[gemma-the-husky]

What about for rouding to x number of decimal places. So if have 3.567889 but want it rounded to the hundreth place (so it should read 3.57)..

Thanks..

in A97 to round to 2dps you have to effectively

1. take a number
2. multiply it by 100
3. round it to an integer
4. divide by 100

so in your example

step1 3.567889
step2 356.7889
step3 357
step4 3.57

hence, all dealt with by

clng(mynum*100)/100

 

[ByteMyzer]

Yes, gemma, until you try it with a number like 4294967296.12345:

? clng(4294967296.12345*100)/100

Run-time error '6':
Overflow


Now, with MY Round function for A97, as shown here:

[COLOR="Navy"]Public Function[/COLOR] Round( _
    [COLOR="navy"]ByVal[/COLOR] Number, _
    [COLOR="navy"]Optional ByVal[/COLOR] NumDigitsAfterDecimal [COLOR="navy"]As Long[/COLOR])

    [COLOR="Navy"]If[/COLOR] IsNumeric(Number) = [COLOR="navy"]True Then[/COLOR]
        Round = [COLOR="navy"]CDbl([/COLOR]Format(Number, "#." _
            & String(NumDigitsAfterDecimal, "#"))[COLOR="navy"])
    End If

End Function[/COLOR]

? Round(4294967296.12345, 2)
4294967296.12

 

[datAdrenaline]

I do like the use of the Format() function, but it is a fairly taxing function, so I use the following to round in A97, as it is about 60% faster ... which is probably imperceptible with a single calc, but when evaluation 10,000 or 100,000 rows of data, the difference will become evident.

Public Function fRound(varNumber As Variant _
                       , Optional intDecimalPlaces As Integer = 0) As Variant
'Round a number to intDecimalPlaces, default is 0 places
     
    fRound = Int(varNumber * (10 ^ intDecimalPlaces) + 0.5) / (10 ^ intDecimalPlaces)
 
End Function

? fRound(4294967296.12345, 2)
4294967296.12

....

Also ... just a quick note about using CLng(), CInt(), as well as Round() in A2000 and up ... they all use "Bankers Rounding" as opposed to "Scientific Rounding". Scientific rounding will "round up" if the significant digit is split by 5 ...

1.125 => 1.13

In Bankers Rounding, you round up on odd numbers, and down on even numbers ..

1.125 => 1.12
1.115 => 1.12

....

One more note ... a Double has 15 significant digits, so with that you can figure out if the "rounding" due to computer unit representation will effect you or not...

 

[ByteMyzer]

This is very true. It all depends on your application as to which solution best fits your requirement.