URGENT's NOT THE WORD! UNRESOLVED

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

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 :p

Regards,

Paul.

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

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.

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.

VB Code:

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

Hi

have you tried FormatNumber:

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

Hope this solve the problem

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.

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

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

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 :)

Quote:

---

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 :D

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.

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:

VB Code:

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

Quote:

---

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:

VB Code:

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

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

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

Quote:

---

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 :D

Quote:

---

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

---

Genius! :D

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! :p

Regards,

Paul.

Quote:

---

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 ;)

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



Paul Moore

Another Paul Moore? Hmm...

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

Something to think about.

Regards,

The original Paul Moore :D

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....