ROUNDING IN ACCESS 1

[SILHOUETE2]

Hi, I am developing a database in Access 2000 for work and I am having trouble with rounding certain numbers in a specific way. I know that there are ROUNDUP, ROUNDDOWN and MROUND functions available in Excel...do they all exist in Access as well? Or is there an AddIn available somewhere? So far I have only been able to find the Round function and all it does is standard rounding. I need certain fields to always round up to the nearest whole number and I have another field that needs to round up to the nearest 1/8th of an inch. Any suggestions on how I might accomplish this?....Any suggestions would be greatly appreciated!!

 

[ETID]

Look to the Int() function....

to round to the 10th ...

=int([field_name]*100)/100

 

[RobBroekhuis]

The nearest eighth of an inch would be

round(8*MyLength)/8

Rob

 

[ETID]

Oooops....

should be cint instead of int

this will round to two decimal places.
(the number of zeros in the formula = the number of decimal places to round to).


=cint([field_name]*100)/100

 

[combo]

Try to make reference to excel's object Application.WorksheetFunction and will have asscss to many excel spreadsheet functions (other than VBA).

For instance:
(Make reference to MS excel object library in tools>references dialog first)

Use excel rounging function:

Sub ExcelFunc()
Dim xlApp As New Excel.Application
MsgBox xlApp.WorksheetFunction.Round(1.25, 1)
End Sub

results in 1.3

This may be slow for huge data, but works!

 

[RobBroekhuis]

The round function is available as a standard VBA function. No need to use a worksheet function in that particular case, but your suggestion to use them in general is a useful one. Both of the problems suggested in the original post can be handled with the standard round function:

1. to round up:

=round(MyValue+0.49999)

2. to round to closest 1/8th inch

=round(MyValue*8)/8

Rob

 

[jebry]

Hi!

The roundup is a little bit stickier than that because standard MS rounding conventions will round odd.5 up and even.5 down so, if the number is such that you end up with 6.5 say then the rounding procedure will make the number 6 instead of 7. So to get a true round up function you would need to do something like this (this is for rounding up to two decimals:

Remainder = YourNumber * 100 - Int(YourNumber* 100)

If Remainder < 0.0001 Then (or whatever value you need)
funcRoundUp = Int(YourNumber* 100) / 100
Else
funcRoundUp = (Int(YourNumber * 100) + 1) / 100
End If

You can of course adjust this to whatever accuracy you want and to any number of decimal places.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[RobBroekhuis]

Interesting! I wasn't aware of MS standard rounding conventions - they seem a bit odd. Anyway, I think by proper choice of the constant (0.49999 in my example) you can get around any complications of that sort. I'd venture to guess that my suggestion works for most applications (it would not work if it's possible to have a value of, say, 7.000001, which would be rounded down rather than up).
Rob

 

[ETID]

Oooops....

should be cint instead of int

this will round to two decimal places.
(the number of zeros in the formula = the number of decimal places to round to).

In access type this in a new field...replacing &quot;field_name_to_round&quot; with your actual field name.


rounded:cint([field_name_to_round]*100)/100

 

[combo]

I,ve tried rounding in excel sheet (XP) - every x.5 is rounded up to x+1 (correctly).

 

[RobBroekhuis]

In Excel worksheet, yes. For some reason, the VBA function is different, exactly as Jeff said.
Rob

 

[mscallisto]

Thanks Jeff ....worth a star....I'd guess Bill Gates didn't know that!

 

[k1bnr34]

Hi, Friends.
How about my idea. I made own module which is &quot;myRound&quot;. You ought to make your own module like me.
****************************************
Public Function myRound(VALUE As Currency, DIGITS As Integer, PROCESSING As Byte) As Double
'--------------------------------------------------------
' Four Operations
' PROCESSING 0:Round 1:Round down 2:Round up
'--------------------------------------------------------
Select Case PROCESSING
Case 0
myRound = Fix(VALUE * (10 ^ DIGITS) + 0.5 * Sgn(VALUE)) / (10 ^ DIGITS)
Case 1
myRound = Fix(VALUE * (10 ^ DIGITS)) / (10 ^ DIGITS)
Case 2
If Fix(VALUE * (10 ^ DIGITS)) = VALUE * (10 ^ DIGITS) Then
myRound = Fix(VALUE * (10 ^ DIGITS)) / (10 ^ DIGITS)
Else
myRound = Fix(VALUE * (10 ^ DIGITS) + 1) / (10 ^ DIGITS)
End If
End Select
End Function
****************************************
Hope this helps.