URGENT's NOT THE WORD! UNRESOLVED

[VisionIT]

Simple... and yet I can't figure it!

I have a number in a field like this...

5.425

I need to round this number up to 5.43, so i used this command...

boxVAT = round(boxvat, 2)

When I run the package it rounds it down to 5.42, which means all the balances are 1 penny out...

HELP.

Anyone any ideas? Don't say add a penny on either

Regards,

Paul.

[Lightning]

Add 0.005 to the value so that the round-up goes is always correct

[VisionIT]

nice idea... but it still doesn't work M8...

The ROUND feature should automatically round up the number if it's end's with x.xx5. Is this a fault with VB?

Thanks guys...

Regards,

Paul.

[mr_metal_hed]

Yup. It evaluates incorrectly. I did a quick search concerning this and found another fellow that wanted an answer. He decided to use this function. It's not mine, so I haven't checked it's speed or efficiency. I hope it helps.

Public Function RoundIt(Number As Variant, NumDigitsAfterDecimal As Long) As Variant
   If Not IsNumeric(Number) Then
       RoundIt = Number
   Else
       RoundIt = Int(0.5 + Number * (10 ^ NumDigitsAfterDecimal)) / (10 ^ NumDigitsAfterDecimal)
   End If
End Function

[quasar_sa]

Hi

have you tried FormatNumber:

FormatNumber(5.075, 2) = 5.08
FormatNumber(5.074,2) = 5.07

Hope this solve the problem

[VisionIT]

Tried that too M8... still didn't work.

Thanks for that code m8, but again... it still refuses to round it properly. I might just get a paper & pen!

ANYONE... PLEASE...

Regards,

Paul.

[mr_metal_hed]

Here is a post at another forum that may provide some help.

[JordanChris]

The best description I have seen of this issue / problem / opportunity is at: http://support.microsoft.com/default...;en-us;q196652

[KayJay]

The issue crops up only when the last number after the decimal point is exactly 5. Less than 5 get rounded down, greater than 5 get rounded up. Best solution is to write Ur own Round function, which changes the last number to 6 or 4 (depending on how U want the rounding done) and then use the inbuilt to round off the resultant number.

HTH

[kleinma]

Originally posted by JordanChris
The best description I have seen of this issue / problem / opportunity is at: http://support.microsoft.com/default...;en-us;q196652

yeah use this link.. if you can't find a round function in here to fit ya.. it probably doesn't exist

that KB article saved me once before

[VisionIT]

Thankyou all for the help... and i've managed to get a one day extension on the package, so i'll keep trying!

All the best,

Paul.

[Frans C]

Kleinma, did you see the following comment:
Visual Basic for Applications does not have any function that does arithmetic rounding.

I once wrote the following function to implement arithmetic rounding:

Public Function MyRound(ByVal Number As Double, Optional ByVal NumDigitsAfterDecimal As Long = 0) As Double
    MyRound = Fix((Number * (10 ^ NumDigitsAfterDecimal)) + (Sgn(Number) * 0.5)) / (10 ^ NumDigitsAfterDecimal)
End Function

[kleinma]

Originally posted by Frans C
Kleinma, did you see the following comment:
Visual Basic for Applications does not have any function that does arithmetic rounding.

I once wrote the following function to implement arithmetic rounding:

Public Function MyRound(ByVal Number As Double, Optional ByVal NumDigitsAfterDecimal As Long = 0) As Double
    MyRound = Fix((Number * (10 ^ NumDigitsAfterDecimal)) + (Sgn(Number) * 0.5)) / (10 ^ NumDigitsAfterDecimal)
End Function

??? huh? are you talking about in that KB article? they have about 10 different types of custom rounding functions

[Frans C]

Sorry, I read the article to fast. I missed the custom functions.
My function is similar to the SymArith function.

[Mav505]

NO sure if this is what you need, I dunno, I never have been great at this VB stuff

I put this on a form with a textbox for ur value, a label for the result and a button to do the crunching stuff:



Private Sub cmdRound_Click()
Dim MyNumber As Currency
Dim MyRoundedNumber As Currency
Dim TestValue As String


MyNumber = CCur(txtNumber.Text)
TestValue = CStr(MyNumber)
If Right(TestValue, 1) = "5" Then
MyNumber = MyNumber + 0.001
End If



MyRoundedNumber = Round(MyNumber, 2)

lblNumber.Caption = CStr(MyRoundedNumber)



End Sub

It seems to work, but try it out


Paul Moore

[kleinma]

Originally posted by Frans C
Sorry, I read the article to fast. I missed the custom functions.
My function is similar to the SymArith function.

its all good.. i just got a little confused for a second

[VisionIT]

Originally posted by Mav505
NO sure if this is what you need, I dunno, I never have been great at this VB stuff

Genius!

I haven't tried it (yet), but the code looks perfect. I'm in the middle of a server crisis ATM, but i'll try it l8er.

Cheers.

What's your address M8y... there's a cheque in the post!

Regards,

Paul.

[KayJay]

Originally posted by Mav505
NO sure if this is what you need, I dunno, I never have been great at this VB stuff

I put this on a form with a textbox for ur value, a label for the result and a button to do the crunching stuff:



Private Sub cmdRound_Click()
Dim MyNumber As Currency
Dim MyRoundedNumber As Currency
Dim TestValue As String


MyNumber = CCur(txtNumber.Text)
TestValue = CStr(MyNumber)
If Right(TestValue, 1) = "5" Then
MyNumber = MyNumber + 0.001
End If



MyRoundedNumber = Round(MyNumber, 2)

lblNumber.Caption = CStr(MyRoundedNumber)



End Sub

It seems to work, but try it out


Paul Moore

My post earlier

[Mav505]

Not a problem sir, always glad to help another Paul Moore (I'm one too ), Hope it works out for ya.



Paul Moore

[VisionIT]

Another Paul Moore? Hmm...

We could start another dave gorman style show ! How many Paul Moore's in the world?

Something to think about.

Regards,

The original Paul Moore

[UnderTheTable]

In vb or any other development tools use the same logic......

if the decimal value is not more than 5 then it will not round with the highest value....

i suppose this is also a mathematical logic....