Hi,
I have been searching and struggling with this for a while. I am looking to Round Up numbers as in Excel. I know Int function can be used as follow:
Int(100 * [MyField]) / 100
Works well, however I want to have different roundings depending on the numbers.
if value is between 0 and 1000, round up to 10
if value is between1000 and 10,000, round up to 100
if value is between 10,000 and 100,000, round up to 1000
etc
I have tried to us IFF functions and nesting the INT() but it gives me an error saying that the formula id too complex.
I also tried to write some code to make a public function based on something like this:
Public Function GlblRoundup(wNumber As Currency, wDecPlaces As Integer) As Currency
Dim wResult As Currency
Dim wFactor As Currency
Select Case wDecPlaces
Case 0
wFactor = -1
Case 1
wFactor = -10
Case 2
wFactor = -100
Case 3
wFactor = -1000
Case 4
wFactor = -10000
Case Else
wFactor = -10000
End Select
wResult = Int(wFactor * wNumber) / wFactor
GlblRoundup = Round(wResult, wDecPlaces)
End Function
But unsuccessful.
Would greatly appreciate if someone could help.
I have been searching and struggling with this for a while. I am looking to Round Up numbers as in Excel. I know Int function can be used as follow:
Int(100 * [MyField]) / 100
Works well, however I want to have different roundings depending on the numbers.
if value is between 0 and 1000, round up to 10
if value is between1000 and 10,000, round up to 100
if value is between 10,000 and 100,000, round up to 1000
etc
I have tried to us IFF functions and nesting the INT() but it gives me an error saying that the formula id too complex.
I also tried to write some code to make a public function based on something like this:
Public Function GlblRoundup(wNumber As Currency, wDecPlaces As Integer) As Currency
Dim wResult As Currency
Dim wFactor As Currency
Select Case wDecPlaces
Case 0
wFactor = -1
Case 1
wFactor = -10
Case 2
wFactor = -100
Case 3
wFactor = -1000
Case 4
wFactor = -10000
Case Else
wFactor = -10000
End Select
wResult = Int(wFactor * wNumber) / wFactor
GlblRoundup = Round(wResult, wDecPlaces)
End Function
But unsuccessful.
Would greatly appreciate if someone could help.