Rounding to nearest whole number

[haleys]

I have been working on a query and I need to round up to the nearest 1000. Example, an employee's Benefit Salary is 24950.00 and I need to round to nearest 1000 so I can determine the insurance pay out, so I want to see in my query the salary rounded to 25000.00. I have tried help and it just talks about rounding after the decimal. In my Access 97 bible it doesn't even address this logic it only explains how to round fractions.

I know this must be able common in the HR world, so why can't I find anything on this type of logic.

 

[Fornatian]

Haleys,

Why not divide by 1000, round the fraction then mutiply by 1000?

 

[ritchieroo]

This function will round to various factors of 10. Specify 3 for 1000, 2 for 100 etc.

Note: If you are rounding to nearest 1000, then 500 will always be rounded up.

Public Function RoundTo(ByVal Value As Variant, ByVal Factor As Long) As Variant
  Value = Value * 100   'Work in pennies for accuracy
  Factor = 10 ^ (Factor + 2)
  If Value Mod Factor >= (Factor \ 2) Then
    RoundTo = (Value + (Factor - (Value Mod Factor))) / 100
  Else
    RoundTo = (Value - (Value Mod Factor)) / 100
  End If
End Function

res = RoundTo(24950.00, 3)