Rounding off currency

[storer]

I want to use an expression to generate a deposit amount due. If the total due is for $1656 and I want to figure a deposit of 30%, when I use
=[totaldue]*.30 i get 496.80

I would like to make the number round off to the next dollar, 497 or even better, be able to round off to the next 10, which in this case would be 500. How can I do that? The fields are all set to currency. I played around with changing the fields to integer which didn't work. I tried changing the currency from auto decimal to 0, which would work but I don't get the currency format that I want. ($1000.00) Any suggestions?

 

[DougP]

Well you can make a function.
You can use the litle known MOD function
Which lets you divide a number and come up with a remainder
Or in your case drop the remainder.
What range of dollars are we talking about?
here is a simple function which just has 1 to 20

Public Function RoundUp(ValueToRound As Currency)
'this is crude but it will work
Dim ValueToTest As Integer
ValueToTest = Int(ValueToRound)
Select Case ValueToTest
Case 0 To 10
RoundUp = 10
Case 10 To 20
RoundUp = 20
' Add more Case's to go as far as you want
End Select

End Function


DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[MichaelRed]

Public Function basRound(AmtIn As Currency, AmtRnd As Single) As Currency

    Dim ModRmdr As Single

    If (AmtIn >= AmtRnd) Then
        ModRmdr = (AmtIn * 100) Mod (AmtRnd * 100)
        basRound = AmtIn + AmtRnd - (ModRmdr / 100)
     Else
        basRound = AmtIn
    End If

End Function

? basRound(496.80, 10.)
500

? basRound(1656 * 0.3, 10)
500

? basRound(1.25, 2.50)
1.25


With three examples?




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over