Currency Round Down Help

[EBee]

Hi,

I need help to round down The Currency to the nearest .25 cents. . i.e if $ 11.49 i want it to round down to $11.25 and if $11.51 i want it to be $11.50. 'em i on the right track. .

thanks
erwin

Sub Round_Down()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("RoundDowntoQuarterstable&quot

Do Until rs.EOF
If rs!Tip > $*.25 and <$*.50 Then

rs.Edit
rs!TipRoundDown = &quot;.25&quot;
rs.Update

End If


rs.MoveNext
Loop

MsgBox &quot; Round Down is Done. . &quot;


End Sub

 

[BoxHead]

Erwin,

Someone else may know of an easier way to do this, but based on your post, I'd suggest declaring a variable so you could run all of your If...Then arguments before updating.

I'd also try looking at just the right two characters in the Tip field with

Right([rs]![Tip],2).

Dim QuarterVal

     If Right([rs]![tip],2) < 25 Then
          QuarterVal = &quot;.00&quot;
     ElseIf Right([rs]![tip],2) > 25 and _
          Right([rs]![tip],2) < 50 Then 
          QuarterVal = &quot;.25&quot;
     ElseIf Right([rs]![tip],2) > 50 and _
          Right([rs]![tip],2) < 75 Then 
          QuarterVal = &quot;.50&quot;
     ElseIf Right([rs]![tip],2) > 75 Then 
          QuarterVal = &quot;.75&quot;
     Else
          QuarterVal = &quot;.&quot; & Right([rs]![tip],2)
     End If
        
        rs.Edit
        rs!TipRoundDown = QuarterVal
        rs.Update

etc...


HTH
John

Use what you have,
Learn what you can,
Create what you need.

 

[EBee]

I am almost there, do i DIM QuarterVal as Currency? I am getting Type missmatch error

this really helps me a lot . . .
thanks
erwin

 

[EBee]

I think i figures out why its not working, my data needs to be $12.05 not $ 12.0589 in my recordset, How can I change this format, I tried to change it from the design view and changed it to currency and 2 decimal places and it still does it.

erwin

 

[BoxHead]

Erwin,

I played around with the format and couldn't get it to work either.

Changing

Right([rs]![Tip],2)

to

     Mid([rs]![Tip] , InStr([rs]![Tip] , ".") + 1, 2)

should solve it.


HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[Craig0201]

Eek.....

Not pleasant code!

Something like:

Function 25Cents(Price as Currency) As Currency

Dim dblCents as Double

dblCents = Price - Int(Price)

If dblCents < 0.25 Then
25Cents = Int(Price)
ElseIf dblCents < 0.5 Then
25Cents = Int(Price) + 0.25
ElseIf dblCents < 0.75 Then
25Cents = Int(Price) + 0.5
Else
25Cents = Int(Price) + 0.75
End If

End Function

 

[EBee]

hi John

your original suggestion works by changing

If Right([rs]![tip],4) < 2500 Then
QuarterVal = &quot;.00&quot;
ElseIf Right([rs]![tip],4) > 2500 and _
Right([rs]![tip],4) < 50 Then
QuarterVal = &quot;.25&quot;
ElseIf Right([rs]![tip],4) > 5000 and _
Right([rs]![tip],4) < 75 Then
QuarterVal = &quot;.50&quot;
ElseIf Right([rs]![tip],4) > 7500 Then
QuarterVal = &quot;.75&quot;
Else
QuarterVal = &quot;.&quot; & Right([rs]![tip],2) * this i need help with, i don't understand what its doing, does this suppose to i give me the value of QuarterVal as .75 12.89. . . .
End If

rs.Edit
rs!TipRoundDown = QuarterVal = this is .75 now i need it to show the original amount with the correct value $ 12.75. . .
rs.Update

thanks
erwin

 

[BoxHead]

Erwin,

The Else statment in the original code was designed to handle the cases where the rst!tip value already had &quot;00&quot;, &quot;25&quot;, &quot;50&quot;, or &quot;75&quot; as the right two characters.

You should be able to accomplish the same thing by changing your 'greater than' statements to 'equal to or greater than'

ElseIf Right([rs]![tip],4)

=

> 2500 and _
          Right([rs]![tip],4) < 50 Then 
          QuarterVal = &quot;.25&quot;
     ElseIf Right([rs]![tip],4)

=

> 5000 and _
          Right([rs]![tip],4) < 75 Then 
          QuarterVal = &quot;.50&quot;
     ElseIf Right([rs]![tip],4)

=

> 7500 Then 
          QuarterVal = ".75"
     End If

The expression to return the newly adjusted value would be

    Left([rs]![tip], Instr([rs]![tip], ".") - 1) & QuarterVal

on one line

Hope this helps

John

Use what you have,
Learn what you can,
Create what you need.

 

[EBee]

The expression to return the newly adjusted value would be
Left([rs]![tip], Instr([rs]![tip], &quot;.&quot - 1) & QuarterVal on one line

Quarterval works and gives me the correct value .25 .50 .75 the line above as it returns gives the following

12.81 = 120.75
11.28 = 110.25

its adding another zero to the left. Is this because it is a type Currency not a string?

thanks for all your help

eriwn

 

[JerryDennison]

You might find it easier to use the following function instead:

Public Function QuarterRound(dblInput As Double) As Currency

Dim dblTemp As Double

dblTemp = dblInput - Int(dblInput)

Select Case dblTemp
Case Is <= 0.24
QuarterRound = Int(dblInput)
Case 0.25 To 0.49
QuarterRound = Int(dblInput) + 0.25
Case 0.5 To 0.74
QuarterRound = Int(dblInput) + 0.5
Case Else
QuarterRound = Int(dblInput) + 0.75
End Select


End Function

 

[BoxHead]

Hey, Erwin, Jerry's looks a whole lot easier! It does it all in one step.

Good job, Jerry.

John

Use what you have,
Learn what you can,
Create what you need.

 

[EBee]

Thank you for all your help , that looks good. I got things to work now. I am making it pretty now with Forms.etc. Now i need to try to get change for the total money collected. maybe you can show me some idea

Total money collected = 980.00
if i have 40 employees and not all of them worked 8 hours
i need to change the collected money to break the split down to each empolyee. next task

thanks

erwin

 

[RegionsRob]

If you wanted to use a NUMERIC field for holding your input data, place the following formula that will round down to the nearest .25 in another (text) field:
=Int(rs!Tip)+((((rs!tip -Int(rs!Tip))*100)\25)*25)/100

If you are calculating a tip, you will have a numeric datatype, so this will act upon that to round the tip down to the nearest quarter.

 

[Craig0201]

Erwin,

Small point but Jerry's code will fail if the decimal you are holding has more then 2dp and falls into the areas .24 to .249999999 and the similar boundaries. Thats why my code used If statements to mark the boundaries.

Craig