rounding in Access

[sadams1970]

is they anyway to round numbers in Access? I'm a relatively advanced Access user, but can't figure this one out.

 

[CT Witter]

Welcome to Access! All of the below samples were taken from MS Neatcode database...old, but well worth the download!! http://www.microsoft.com/downloads/...2e-553f-4d1b-861b-48accc2f40c8&displaylang=en

Just use which every code best suits your needs!!


**To use these you will need to place the code in a Module. Go to Module Tab and select new module.

To use in a query, use the syntax...
Rounded: Ceiling([Field you want to Round])

or

Rounded: Round2CB([Field you want to Round])**

Function Ceiling(N, ByVal Precision)
'
' Similar to Excel's Ceiling function
' Rounds up to the next higher level of precision.
' Precision cannot be 0.
'
Dim Temp As Double
  Precision = Abs(Precision)
  Temp = Int(N / Precision) * Precision
  If Temp = N Then
    Ceiling = N
  Else
    Ceiling = Temp + Precision * Sgn(Temp)
  End If
End Function


Function Floor(N, ByVal Precision)
'
' Similar to Excel's Floor function
' Rounds down (toward zero) to the next higher level of precision.
' Precision cannot be 0.
'
  Precision = Abs(Precision)
  Floor = Int(N / Precision) * Precision
End Function


Function Round2(X)
'
' Rounds a number to 2 decimal places
' Uses arithmatic rounding
'
  Round2 = Int(X * 100 + 0.5) / 100
End Function

Function Round2C(X)
'
' Rounds number to 2 decimal places
' Uses arithmatic rounding
' Designed for use with Currency values
'
  If IsNull(X) Then
    Round2C = Null
  Else
    Round2C = CCur(Int(X * 100 + 0.5) / 100)
  End If
End Function

Function Round2CB(X As Variant) As Variant
'
' Banker's rounding of Currency to 2 decimal places.
'
Dim Temp As Currency, ITemp As Currency, Digit As Integer
  If IsNull(X) Then Exit Function
  Round2CB = CCur(X / 100) * 100
End Function

Function RoundN(X, N As Integer)
'
' Rounds a number to N decimal places
' Uses arithmatic rounding
' N should be in the range 0-10 for proper results
'
Dim Factor As Long
  Factor = 10 ^ N
  RoundN = Int(X * Factor + 0.5) / Factor
End Function

 

[sadams1970]

Thanks for the help!

Problem: I've tried several of the functions & they all lop off all the decimals leaving only an integer. I use an "update" query. Am I doing something wrong?

 

[CT Witter]

Post the sql that you are using in your query.

And if you are running an update query, make sure that the field in the underlying table supports non-integer numbers (ie, single, double)etc.

 

[sadams1970]

got it! Thanks!

One more...I want to run a make-table query to import data from a table...this will be a duplicate copy but I want the column headers to be different in the new table. Is there a way to do this?

Thanks again!