round-to-even logic

[Johnie Karr]

Hello,

//Begin Quote
The Round function utilizes round-to-even logic. If the expression that you
are rounding ends with a 5, the Round function will round the expression so
that the last digit is an even number. For example:

Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)

In these cases, the last digit after rounding is always an even number. So,
be sure to only use the Round function if this is your desired result.
//End Quote

If the number before the 5 is even it will round down, if the number before
the even is odd it will round up.

Does anyone know a work-around for this issue?

Thanks,

 

[Marshall Barton]

//Begin Quote
The Round function utilizes round-to-even logic. If the expression that you
are rounding ends with a 5, the Round function will round the expression so
that the last digit is an even number. For example:

Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)

In these cases, the last digit after rounding is always an even number. So,
be sure to only use the Round function if this is your desired result.
//End Quote

If the number before the 5 is even it will round down, if the number before
the even is odd it will round up.

Does anyone know a work-around for this issue?

That's not an issue, it actually more accurate than the
grade school method of always rounding up the number before
the 5.

If that's what you really want to do, then you can create
your own function to do what you want:

rounded = Fix(num * 100 + Sgn(num) * .500001) / 100

 

[Johnie Karr]

Ok,

The solution paritially works. Here is my code:

Private Sub testRound_AfterUpdate()
Me!testRound = (Me!SubTotal * Me!testRound) / 100
testRound2 = Round(Me!testRound, 2)
Me!testRound = Fix(testRound2 * 100 + Sgn(testRound2) * 0.500001) / 100
End Sub

if Me!SubTotal = 2.235 it rounds up appropriatly to 2.4 but if Me!SubTotal =
2.245 it stays at 2.24 instead of going to 2.25.

Any suggestions?

Thanks,
Johnie Karr

 

[Sreedhar]

Hi, this may not be a direct solution, but take a look at this neverthless.
You may modify to include decimals.

Caution: I've not tested the results

Function fRound(ValueToRound As Variant, _
RoundMethod As String) As Variant
On Error GoTo ErrHandle

fRound = Round(ValueToRound, 0)

Select Case RoundMethod
Case "Up"
If ValueToRound - fRound > 0 Then fRound = fRound + 1
Case "Down"
If fRound - ValueToRound > 0 Then fRound = fRound - 1
Case "Even"
'Don't need any code here
End Select

ExitHandle:
Exit Function

ErrHandle:
MsgBox Err.Description
Resume ExitHandle

End Function

--
Sreedhar

Ok,

The solution paritially works. Here is my code:

Private Sub testRound_AfterUpdate()
Me!testRound = (Me!SubTotal * Me!testRound) / 100
testRound2 = Round(Me!testRound, 2)
Me!testRound = Fix(testRound2 * 100 + Sgn(testRound2) * 0.500001) / 100
End Sub

if Me!SubTotal = 2.235 it rounds up appropriatly to 2.4 but if Me!SubTotal =
2.245 it stays at 2.24 instead of going to 2.25.

Any suggestions?

Thanks,
Johnie Karr

That's not an issue, it actually more accurate than the
grade school method of always rounding up the number before
the 5.

If that's what you really want to do, then you can create
your own function to do what you want:

rounded = Fix(num * 100 + Sgn(num) * .500001) / 100

 

[Marshall Barton]

How can the other code successfully round a value one way
after you have rounded it the other way?

I think you should remove the Round function from the line:
testRound2 = Round(Me!testRound, 2)

However, I can't figure out what the line:
Me!testRound = (Me!SubTotal * Me!testRound) / 100
is supposed to do, so I can't be sure of anything here.
--
Marsh
MVP [MS Access]

The solution paritially works. Here is my code:

Private Sub testRound_AfterUpdate()
Me!testRound = (Me!SubTotal * Me!testRound) / 100
testRound2 = Round(Me!testRound, 2)
Me!testRound = Fix(testRound2 * 100 + Sgn(testRound2) * 0.500001) / 100
End Sub

if Me!SubTotal = 2.235 it rounds up appropriatly to 2.4 but if Me!SubTotal =
2.245 it stays at 2.24 instead of going to 2.25.

That's not an issue, it actually more accurate than the
grade school method of always rounding up the number before
the 5.

If that's what you really want to do, then you can create
your own function to do what you want:

rounded = Fix(num * 100 + Sgn(num) * .500001) / 100

 

[Johnie Karr]

I did take out the round function.

Let me explain my code.

I have a field called testRound which is simulating the surcharge and in
that field I tupe '3' which stands for 3 percent. Then I take that and
multiply it by the subtotal and divide by 100 to get 3 percent of the
subtotal as a surcharge.

This final value is what I need to be rounded.

Thanks,

How can the other code successfully round a value one way
after you have rounded it the other way?

I think you should remove the Round function from the line:
testRound2 = Round(Me!testRound, 2)

However, I can't figure out what the line:
Me!testRound = (Me!SubTotal * Me!testRound) / 100
is supposed to do, so I can't be sure of anything here.
--
Marsh
MVP [MS Access]

The solution paritially works. Here is my code:

Private Sub testRound_AfterUpdate()
Me!testRound = (Me!SubTotal * Me!testRound) / 100
testRound2 = Round(Me!testRound, 2)
Me!testRound = Fix(testRound2 * 100 + Sgn(testRound2) * 0.500001) / 100
End Sub

if Me!SubTotal = 2.235 it rounds up appropriatly to 2.4 but if Me!SubTotal =
2.245 it stays at 2.24 instead of going to 2.25.

Johnie Karr wrote:
//Begin Quote
The Round function utilizes round-to-even logic. If the expression that you
are rounding ends with a 5, the Round function will round the expression so
that the last digit is an even number. For example:

Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)

In these cases, the last digit after rounding is always an even number. So,
be sure to only use the Round function if this is your desired result.
//End Quote

If the number before the 5 is even it will round down, if the number before
the even is odd it will round up.

Does anyone know a work-around for this issue?


That's not an issue, it actually more accurate than the
grade school method of always rounding up the number before
the 5.

If that's what you really want to do, then you can create
your own function to do what you want:

rounded = Fix(num * 100 + Sgn(num) * .500001) / 100

 

[Marshall Barton]

This code should do what I think you want:

Dim testRound2 As Double 'Currency??
testRound2 = (Me!SubTotal * Me!testRound) / 100
Me!testRound = Fix(testRound2 * 100 _
+ Sgn(testRound2) * 0.500001) / 100
--
Marsh
MVP [MS Access]

I did take out the round function.

Let me explain my code.

I have a field called testRound which is simulating the surcharge and in
that field I tupe '3' which stands for 3 percent. Then I take that and
multiply it by the subtotal and divide by 100 to get 3 percent of the
subtotal as a surcharge.

This final value is what I need to be rounded.

How can the other code successfully round a value one way
after you have rounded it the other way?

I think you should remove the Round function from the line:
testRound2 = Round(Me!testRound, 2)

However, I can't figure out what the line:
Me!testRound = (Me!SubTotal * Me!testRound) / 100
is supposed to do, so I can't be sure of anything here.

The solution paritially works. Here is my code:

Private Sub testRound_AfterUpdate()
Me!testRound = (Me!SubTotal * Me!testRound) / 100
testRound2 = Round(Me!testRound, 2)
Me!testRound = Fix(testRound2 * 100 + Sgn(testRound2) * 0.500001) / 100
End Sub

if Me!SubTotal = 2.235 it rounds up appropriatly to 2.4 but if Me!SubTotal =
2.245 it stays at 2.24 instead of going to 2.25.


:

Johnie Karr wrote:
//Begin Quote
The Round function utilizes round-to-even logic. If the expression that you
are rounding ends with a 5, the Round function will round the expression so
that the last digit is an even number. For example:

Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)

In these cases, the last digit after rounding is always an even number. So,
be sure to only use the Round function if this is your desired result.
//End Quote

If the number before the 5 is even it will round down, if the number before
the even is odd it will round up.

Does anyone know a work-around for this issue?


That's not an issue, it actually more accurate than the
grade school method of always rounding up the number before
the 5.

If that's what you really want to do, then you can create
your own function to do what you want:

rounded = Fix(num * 100 + Sgn(num) * .500001) / 100

 

[Johnie Karr]

Marshall,

Thanks alot. That helped. It is working now!

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

This code should do what I think you want:

Dim testRound2 As Double 'Currency??
testRound2 = (Me!SubTotal * Me!testRound) / 100
Me!testRound = Fix(testRound2 * 100 _
+ Sgn(testRound2) * 0.500001) / 100
--
Marsh
MVP [MS Access]

I did take out the round function.

Let me explain my code.

I have a field called testRound which is simulating the surcharge and in
that field I tupe '3' which stands for 3 percent. Then I take that and
multiply it by the subtotal and divide by 100 to get 3 percent of the
subtotal as a surcharge.

This final value is what I need to be rounded.

How can the other code successfully round a value one way
after you have rounded it the other way?

I think you should remove the Round function from the line:
testRound2 = Round(Me!testRound, 2)

However, I can't figure out what the line:
Me!testRound = (Me!SubTotal * Me!testRound) / 100
is supposed to do, so I can't be sure of anything here.


Johnie Karr wrote:
The solution paritially works. Here is my code:

Private Sub testRound_AfterUpdate()
Me!testRound = (Me!SubTotal * Me!testRound) / 100
testRound2 = Round(Me!testRound, 2)
Me!testRound = Fix(testRound2 * 100 + Sgn(testRound2) * 0.500001) / 100
End Sub

if Me!SubTotal = 2.235 it rounds up appropriatly to 2.4 but if Me!SubTotal =
2.245 it stays at 2.24 instead of going to 2.25.


:

Johnie Karr wrote:
//Begin Quote
The Round function utilizes round-to-even logic. If the expression that you
are rounding ends with a 5, the Round function will round the expression so
that the last digit is an even number. For example:

Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)

In these cases, the last digit after rounding is always an even number. So,
be sure to only use the Round function if this is your desired result.
//End Quote

If the number before the 5 is even it will round down, if the number before
the even is odd it will round up.

Does anyone know a work-around for this issue?


That's not an issue, it actually more accurate than the
grade school method of always rounding up the number before
the 5.

If that's what you really want to do, then you can create
your own function to do what you want:

rounded = Fix(num * 100 + Sgn(num) * .500001) / 100