Currency Round off 1

[giyer555]

Using VB 6 & sql 7 I need to round up currency having three decimal places to the nearest 50's
eg: 50.260 = 50.300
50.135 = 50.100
50.999 = 51.000
50.150 = 50.150
50.200 = 50.200
50.380 = 50.400
etc

Can anyone help please?

 

[RayGibbins]

In SQL : select CEILING(50.260 * 10) / 10.0 as per

 

[giyer555]

hi!!! raygibbins Thanx for the reply
the reply given doesn't serve my purpose as the figures are user input through a maskedbox in a VB form & i want the rounded off data to be saved in the db.

 

[RayGibbins]

Heres a bit of VB code

Note: there is no error checking

Dim strValue As String

strValue = CInt(Text1.Text * 100)

Select Case Left(strValue, 1)

Case 1, 2, 3, 4
Mid(strValue, Len(strValue) - 1, 1) = "5"
Case 5, 6, 7, 8, 9
Mid(strValue, Len(strValue) - 1, 1) = "0"

End Select

Text1.Text = strValue / 100

 

[giyer555]

hi!!! raygibbins

Thanx for the reply.
friend the select case should be right(strValue, 1)
but the desired result is not achieved

Wat I need is to round up currency having three decimal places to the nearest 50's
eg: 50.260 = 50.300
50.135 = 50.100
50.999 = 51.000
50.150 = 50.150
50.200 = 50.200
50.380 = 50.400
etc

As per ur code after changing to right(strValue, 1) and

Case 1, 2, 3, 4
Mid(strValue, Len(strValue) - 1, 1) = "0" (not5)
Case 5, 6, 7, 8, 9
Mid(strValue, Len(strValue) - 1, 1) = "5" (not0)
Mid(strValue, Len(strValue), 1) = "0"
the result is not correct
for eg: 25.465 should become 25.500
25.250 should become 25.250
25.525 = 25.500
Please check the value of the last 2 decimal places. Awaiting a favourable response.

Thanx
giyer555

 

[RayGibbins]

Not the best bit of code, but I believe it works ....


Dim strValue As String
Dim strDecimal As String
Dim intValue As String


strValue = CLng(Text1.Text * 1000)
intValue = Fix(Text1.Text)

Select Case CInt(Right(strValue, 3))

Case 0
strDecimal = "000"
Case 1 To 50
strDecimal = "050"
Case 51 To 100
strDecimal = "100"
Case 101 To 150
strDecimal = "150"
Case 151 To 200
strDecimal = "200"
Case 201 To 250
strDecimal = "250"
Case 251 To 300
strDecimal = "300"
Case 301 To 350
strDecimal = "350"
Case 351 To 400
strDecimal = "400"
Case 401 To 450
strDecimal = "450"
Case 451 To 500
strDecimal = "500"
Case 501 To 550
strDecimal = "550"
Case 551 To 600
strDecimal = "600"
Case 601 To 650
strDecimal = "650"
Case 651 To 700
strDecimal = "700"
Case 701 To 750
strDecimal = "750"
Case 751 To 800
strDecimal = "800"
Case 801 To 850
strDecimal = "850"
Case 851 To 900
strDecimal = "900"
Case 901 To 950
strDecimal = "950"
Case 951 To 999
strDecimal = "000"
intValue = intValue + 1

End Select

Text1.Text = intValue & "." & strDecimal

 

[CCLINT]

giyer555, something seems wrong with your rounding logic, or I didn't get enough sleep last night

You Say the currency needs to Round up but then you present use with this:
50.135 = 50.100
25.525 = 25.500
and it seems you have rounded down.

Also, would this mean that 0.035 rounds to 0?

I would think you want to round up as:
50.135 = 50.150
25.525 = 25.550

So, I ask, what should these round to?

0.015
0.035
50.110
50.120
50.124
50.125
50.126
50.130
50.140
50.145
50.155
50.160

 

[giyer555]

Hi!!! cclint

Thanx for the reply...
I am sorry as could not put my q in a proper explanatory manner

0.015 = 0.000
0.035 = 0.000
50.110 = 50.100
50.120 = 50.100
50.124 = 50.100
50.125 = 50.100
50.126 = 50.100
50.130 = 50.100
50.140 = 50.100
50.145 = 50.100
50.155 = 50.200
50.160 = 50.200

Pls note the last 2 decimal places if its below 50 then it should become 00 & if its above 50 then the 3rd decimal place should added with 1. for eg: 25.365=25.400 and 25.961=26.000 and 25.445 = 25.400. Pls note the last 2 decimal places

I hope u got my point.

 

[giyer555]

hi!!! raygibbins Thanx for the reply

With the help of ur second reply in which u have used strValue = CInt(Text1.Text * 100)
i have managed to achieve the desired result by using the if condition the code is as follows
Please take note how i am using this in my proj

Event text1_lostfocus

On Error Resume Next
Dim strValue As String
Dim strdec As String
If txtothrs.Text <> &quot;&quot; Then
text1.Text = (val(mskbasic.Text) / 240) * 125 / 100 * (val(txtothrs))
text1.Text = Round(text1.Text, 3)
End If
'After the above calculations i am rounding off

strValue = CInt(text1.Text * 100)
If Right(strValue, 1) > 5 And Right(strValue, 1) <= 9 Then
strdec = Mid(strValue, Len(strValue) - 1, 1)
Mid(strValue, Len(strValue) - 1, 1) = strdec + 1
Mid(strValue, Len(strValue), 1) = &quot;0&quot;
ElseIf Right(strValue, 1) < 5 Then
strdec = Mid(strValue, Len(strValue) - 1, 1)
Mid(strValue, Len(strValue) - 1, 1) = strdec
Mid(strValue, Len(strValue), 1) = &quot;0&quot;
End If
text1.Text = strValue / 100

The only prob with the above code is that it does not work if the 3rd is 9 for eg: 25.961 should become 26.000 but with the above code it become 25.000
pls help me out with this one or
If u can suggest any better code than the above then do let me know

 

[Golom]

I haven't been wrestling with this as long as the rest of you so maybe I've missed some critical nuance. I did take the list of input & rounded values from giyer555's last post and ran them through this simple formula

Rounded = Val(Format(UnRounded, &quot;0.0&quot)

and I got the same results as his list says we should.

 

[Golom]

... and

Rounded = Round(UnRounded, 1)

also works.

 

[CCLINT]

Ok giyer555. Try this (If it is not exactly like you intend, then I think you can adjust it easily enough):
[blue]

Function RoundXtra(ByVal Number As Currency) As Currency
    Dim x As Currency
    x = Number * 10
    x = (x - Int(x)) * 100
    
    If x > 50 Then
        RoundXtra = Int((Number + 0.05) * 10) / 10
    ElseIf x > 0 And x < 50 Then
        RoundXtra = Int(Number * 10) / 10
    Else
        RoundXtra = Number
    End If
End Function

[black]

Golom: My question was to clear up some other factors, and didn't include all criteria (wasn't needed as it was already explained).

The way I understand it now, is that giyer555 wants the last two digits to Round down if less than 50, round up if more than 50, and if 50 or 00 then remain as 50 or 00.

So:
50.000 = 50.000
50.050 = 50.050 *
50.100 = 50.100
50.150 = 50.150 *
50.200 = 50.200

BUT:
50.010 = 50.000
50.049 = 50.000 *
50.051 = 50.100
50.099 = 50.100

* = The normal round function will not work for these.

To do it as you have shown, you would need to round with the Format function anyways (or use another math. eq.), and NOT with the ROUND(), CINT(), CLNG(), etc. functions as have been used above, as these do not round as you may think, which have been discussed heavily many times in this forum, so I will not go into that now, but you can see for yourself:

(Statistical rounding)
?Round(50.15, 1)
?Round(50.25, 1)
?CInt(51.5)
?CInt(52.5)

(Financial rounding)
?Format$(50.15,&quot;0.0&quot
?Format$(50.25,&quot;0.0&quot
?Format$(51.5,&quot;0&quot
?Format$(52.5,&quot;0&quot

 

[johnwm]

To round up to the next .05 and format to 3dp use:

myNewNum = Format((Int(myOldNum*20)+1)/20,&quot;#.000&quot

This is a simple extension of the old (Int(x * 10) + .5)/10 for rounding to 1 dp, but using 20 as a multiplier and 1 as the addition takes you to the next 5 cents up

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

johnwm, I understand the task to be different...
I do not think 50.500 is to be rounded up to 50.550, but remains 50.500

Therefore, you will see I am using the same logic ((Int(x * 10) + .5)/10), but only for a certain condition.

 

[johnwm]

Sorry CC, I may have mis-read the original question

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

No problem johnwm!
>same logic
Well, not the same logic because of the particular situation, so it was changed slightly (rounds up ONLY if > 50)

It still may be wrong...only giyer555 can tell.