Rounding $ to dime, quarter, dollar 2

[dpav29]

I want to round dollar amounts to the nearest dime, quarter or dollar depending on the amount:

< $10 to nearest dime
$10.01 to $25 to nearest Quarter
$25.01 to $50 to nearest 50 cents
>$50 to nearest dollar

The excel formula to make it happen is (where x is the cell): =IF(x<10,(ROUND(x,1)),(IF(x<25,(ROUND(x*4,0)/4),(IF(x<50,(ROUND(x*2,0)/2),ROUND(x,0))))))

I can't figure out how to convert this to access module. Any suggestions?

 

[JoeMiller]

You can use a select statement to get the right answer. Depending on the version of access you might have the Round function, I believe it's in 2000 up. You'll have to consult help for Round()'s usage as I run 97 and don't have it available to me.

[tt]
Select Case x
Case Is x < 10
Round(x)
Case Is x Between 10.01 and 25
Round(x)
Case Is x Between 25.01 and 50
Round(x)
Case is x > 50
Round(x)
End Select
[/tt]

HTH Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

[look at this approach. It always rounds UP to the value. For Retail purposes, it is generally favored. In other situations, it is not necessarily advised.

Public Function RndToCurr(COST As Single) As Currency

    '< $10 to nearest dime
    '$10.01 to $25 to nearest Quarter
    '$25.01 to $50 to nearest 50 cents
    '>$50 to nearest dollar

    Dim ModRmdr As Single
    Dim RndTo As Single

    Select Case COST
        Case Is <= 10#
            RndTo = 0.1

        Case 10.01 To 25
            RndTo = 0.25

        Case 25.01 To 50#
            RndTo = 0.5

        Case Is > 10#
            RndTo = 0.1
    End Select

    ModRmdr = (COST * 100) Mod (RndTo * 100)
    RndToCurr = COST + RndTo - (ModRmdr / 100)

End Function
[code] MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[dpav29]

Michael,

What would it look like if I wanted to round to nearest (not just RoundUp)?

 

[MichaelRed]

Public Function basYrsMnthsDays(DOB As Date) As String

    Dim Yrs As Integer
    Dim Mnths As Integer
    Dim Days As Integer
    Dim BdFlg As Boolean

    Yrs = DateDiff("yyyy", DOB, Now)
    Mnths = DateDiff("m", DOB, Now) Mod 12


    Select Case Month(DOB)
        Case Is < Month(Now)        'Not yet to this years B. Day

        Case Is = Month(Now)        'Correct Month.  check day
            Select Case Day(DOB)
                Case Is > Day(Now)  'Not there - YET
                    Yrs = Yrs - 1
                    Mnths = 11

                Case Is = Day(Now)  'Special!  this IS the BD
                    basYrsMnthsDays = CStr(Yrs) & &quot; Exactly!&quot;
                    BdFlg = True

                Case Is < Day(Now)  'Just Past it!  All Default Calcs are O.K.

            End Select

        Case Is > Month(Now)
            Yrs = Yrs - 1

    End Select
    
    If (Not BdFlg) Then
            Days = Day(DateSerial(Year(DOB), Month(DOB) + 1, 0) - Day(DOB))
            Days = Days + Day(Now)

            basYrsMnthsDays = CStr(Yrs) & &quot; Years, &quot; & CStr(Mnths) & &quot; Months, and &quot; & Days & &quot; Days&quot;
    End If

End Function
Public Function basRoundVar(AmtIn As Currency) As Currency

    Dim AmtTemp As Single

    Select Case AmtIn
        Case Is < 10#
            basRoundVar = Round(AmtIn, 1)

        Case 10 To 25
            AmtTemp = AmtIn * 100 Mod 25
            If (AmtTemp > 12) Then
                'Round UP
                AmtTemp = 25 - AmtTemp
                basRoundVar = AmtIn + AmtTemp / 100
             Else
                basRoundVar = AmtIn - AmtTemp / 100
                'Round Doun
            End If
                

        Case 25.01 To 50
            AmtTemp = AmtIn * 100 Mod 50
            If (AmtTemp > 25) Then
                'Round UP
                AmtTemp = 50 - AmtTemp
                basRoundVar = AmtIn + AmtTemp / 100
             Else
                basRoundVar = AmtIn - AmtTemp / 100
                'Round Doun
            End If

        Case Is > 50
            basRoundVar = Round(AmtIn)

    End Select

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over