Rounding

[Guest]

I need to calculate a price each to the nearest 1/10th of a cent:
eg:
1.0725 to round to 1.073 (I get 1.072)
1.5145 to round to 1.515 (I get 2.514)
I have rounding set to 3 decimal places but Access 2003 wants to use bankers
rounding and none of the functions in kb/196652 makes any difference in the
result.

 

[dannis]

You can use the Format function to get the correct result, e.g:
Format(2.344,".00")

"MarthaPCS" <[email protected]> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news[email protected]...

 

[Guest]

You mean in lieu of using the "Round, 3" in the query expression somehow, or
in a module? and the expression is "Each: Avg(Round((([Add to Price]+[Order
Premium/Discount])*[Wendt Factor]/1000),3))" When I tried to substitue the
"Format" for "Round", it didn't like it. And looking at other posts, I tried
formatting the column in the query; that didn't work, either. And woulldn't
I need to show 3 decimal places in quotes instead of the two in your example?

 

[John W. Vinson]

I need to calculate a price each to the nearest 1/10th of a cent:
eg:
1.0725 to round to 1.073 (I get 1.072)
1.5145 to round to 1.515 (I get 2.514)
I have rounding set to 3 decimal places but Access 2003 wants to use bankers
rounding and none of the functions in kb/196652 makes any difference in the
result.

Try (CInt(100*[fieldname])/100

John W. Vinson [MVP]

 

[Guest]

Now it rounds to 2 places and only shows a zero for the 3rd fixed decimal
place.

I need to calculate a price each to the nearest 1/10th of a cent:
eg:
1.0725 to round to 1.073 (I get 1.072)
1.5145 to round to 1.515 (I get 2.514)
I have rounding set to 3 decimal places but Access 2003 wants to use bankers
rounding and none of the functions in kb/196652 makes any difference in the
result.

Try (CInt(100*[fieldname])/100

John W. Vinson [MVP]

 

[James A. Fortune]

I need to calculate a price each to the nearest 1/10th of a cent:
eg:
1.0725 to round to 1.073 (I get 1.072)
1.5145 to round to 1.515 (I get 2.514)
I have rounding set to 3 decimal places but Access 2003 wants to use bankers
rounding and none of the functions in kb/196652 makes any difference in the
result.

This turned out to be more interesting than I thought.

In a query:

Rounded: Int(1000 * [fieldname] + 0.5) / 1000

1.0725 -> 1.073
1.5145 -> 1.515

In VBA (Note: Rnd1, Rnd2, Rnd3 and Rnd4 are alternative rounding
functions, not rounding to that number of places):

Public Function Rnd1(varX As Variant) As Double
Rnd1 = Int(1000# * varX + 0.5) / 1000#
End Function

Rnd1(1.0725) -> 1.073
Rnd1(1.5145) -> 1.515

Public Function Rnd2(dblX As Double) As Double
Rnd2 = Int(1000# * dblX + 0.5) / 1000#
End Function

gives

Rnd2(1.0725) -> 1.073
Rnd2(1.5145) -> 1.514

but

Public Function Rnd3(dblX As Double) As Double
Rnd3 = Int(1000# * CDec(dblX) + 0.5) / 1000#
End Function

gives

Rnd3(1.0725) -> 1.073
Rnd3(1.5145) -> 1.515

Public Function Rnd4(dblX as Double) As Double
Rnd4 = Int(1000# * dblX + 0.500000000000001) / 1000#
End Function

might work, but fails on esthetic grounds.

I think you should do some testing to make sure your discontinuities
behave the way you want.

I actually drew pictures of Int(x), Int(x*1000)/1000 and
Int((x+0.0005)*1000)/1000 (along with filled and unfilled endpoints) to
convince myself that the final function should round to the nearest
0.001 with the midpoint rounding up. Error in the floating point
representation seems to have made 1.5145 look more like 1.5144999999999
with Rnd2().

James A. Fortune
(e-mail address removed)

 

[John W. Vinson]

Now it rounds to 2 places and only shows a zero for the 3rd fixed decimal
place.

sorry - use 1000 in place of 100.

John W. Vinson [MVP]

 

[dannis]

I don't quite understand you troubles...
It works both in module and query in number expressions like you've shown .
I've tried just now at least in Access 2003, it's all right.
You can also use a parameter (in query) or variable (in module, String) to
define a number of decimal places.


"MarthaPCS" <[email protected]> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:

You mean in lieu of using the "Round, 3" in the query expression somehow,
or
in a module? and the expression is "Each: Avg(Round((([Add to
Price]+[Order
Premium/Discount])*[Wendt Factor]/1000),3))" When I tried to substitue
the
"Format" for "Round", it didn't like it. And looking at other posts, I
tried
formatting the column in the query; that didn't work, either. And
woulldn't
I need to show 3 decimal places in quotes instead of the two in your
example?

You can use the Format function to get the correct result, e.g:
Format(2.344,".00")

"MarthaPCS" <[email protected]> OIIAYEI/OIIAYEIA ?
II?IOONE OIAAOAYAA:

 

[Guest]

Thanks for the explicit entries for the functions. I'm anxious to give Rnd1
and/or Rnd3 a try as soon as I gain "exclusive access" to the DB. We have 4
or 5 others who are in and out of it all day (it is used to finished goods
Inventory and invoicing). Wonder if Microsoft uses this forum to program
upgrades in their software? It is nice to have such a nice resource for all
us users! Will let you know next week which (hopefully) worked best.
Martha

I need to calculate a price each to the nearest 1/10th of a cent:
eg:
1.0725 to round to 1.073 (I get 1.072)
1.5145 to round to 1.515 (I get 2.514)
I have rounding set to 3 decimal places but Access 2003 wants to use bankers
rounding and none of the functions in kb/196652 makes any difference in the
result.

This turned out to be more interesting than I thought.

In a query:

Rounded: Int(1000 * [fieldname] + 0.5) / 1000

1.0725 -> 1.073
1.5145 -> 1.515

In VBA (Note: Rnd1, Rnd2, Rnd3 and Rnd4 are alternative rounding
functions, not rounding to that number of places):

Public Function Rnd1(varX As Variant) As Double
Rnd1 = Int(1000# * varX + 0.5) / 1000#
End Function

Rnd1(1.0725) -> 1.073
Rnd1(1.5145) -> 1.515

Public Function Rnd2(dblX As Double) As Double
Rnd2 = Int(1000# * dblX + 0.5) / 1000#
End Function

gives

Rnd2(1.0725) -> 1.073
Rnd2(1.5145) -> 1.514

but

Public Function Rnd3(dblX As Double) As Double
Rnd3 = Int(1000# * CDec(dblX) + 0.5) / 1000#
End Function

gives

Rnd3(1.0725) -> 1.073
Rnd3(1.5145) -> 1.515

Public Function Rnd4(dblX as Double) As Double
Rnd4 = Int(1000# * dblX + 0.500000000000001) / 1000#
End Function

might work, but fails on esthetic grounds.

I think you should do some testing to make sure your discontinuities
behave the way you want.

I actually drew pictures of Int(x), Int(x*1000)/1000 and
Int((x+0.0005)*1000)/1000 (along with filled and unfilled endpoints) to
convince myself that the final function should round to the nearest
0.001 with the midpoint rounding up. Error in the floating point
representation seems to have made 1.5145 look more like 1.5144999999999
with Rnd2().

James A. Fortune
(e-mail address removed)

 

[Guest]

Finally got to work on the modules. Your solution worked great after I
thought to check the "on open" event procedure and correct the query event
source under the report properties. Took a while to figure out why the qry
didn't match the report! Thanks, again!
Martha

I need to calculate a price each to the nearest 1/10th of a cent:
eg:
1.0725 to round to 1.073 (I get 1.072)
1.5145 to round to 1.515 (I get 2.514)
I have rounding set to 3 decimal places but Access 2003 wants to use bankers
rounding and none of the functions in kb/196652 makes any difference in the
result.

This turned out to be more interesting than I thought.

In a query:

Rounded: Int(1000 * [fieldname] + 0.5) / 1000

1.0725 -> 1.073
1.5145 -> 1.515

In VBA (Note: Rnd1, Rnd2, Rnd3 and Rnd4 are alternative rounding
functions, not rounding to that number of places):

Public Function Rnd1(varX As Variant) As Double
Rnd1 = Int(1000# * varX + 0.5) / 1000#
End Function

Rnd1(1.0725) -> 1.073
Rnd1(1.5145) -> 1.515

Public Function Rnd2(dblX As Double) As Double
Rnd2 = Int(1000# * dblX + 0.5) / 1000#
End Function

gives

Rnd2(1.0725) -> 1.073
Rnd2(1.5145) -> 1.514

but

Public Function Rnd3(dblX As Double) As Double
Rnd3 = Int(1000# * CDec(dblX) + 0.5) / 1000#
End Function

gives

Rnd3(1.0725) -> 1.073
Rnd3(1.5145) -> 1.515

Public Function Rnd4(dblX as Double) As Double
Rnd4 = Int(1000# * dblX + 0.500000000000001) / 1000#
End Function

might work, but fails on esthetic grounds.

I think you should do some testing to make sure your discontinuities
behave the way you want.

I actually drew pictures of Int(x), Int(x*1000)/1000 and
Int((x+0.0005)*1000)/1000 (along with filled and unfilled endpoints) to
convince myself that the final function should round to the nearest
0.001 with the midpoint rounding up. Error in the floating point
representation seems to have made 1.5145 look more like 1.5144999999999
with Rnd2().

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Finally got to work on the modules. Your solution worked great after I
thought to check the "on open" event procedure and correct the query event
source under the report properties. Took a while to figure out why the qry
didn't match the report! Thanks, again!
Martha

MarthaPCS,

I'm glad you got it to work. Just don't refer to me as a resource.
That appellation has lost some of it's luster lately .

James A. Fortune
(e-mail address removed)