Rounding/Remainder help

[Lord Madrid]

Something that seems easy, but I'm having a hard time figuring it out
for some reason...
For a rounding issue at work, we need to round up to the penny (out
from 5 decimal places). The easist way I can think of, would be to read
the remainder, and if it's greater than 3, add .01. I can't find a way
to read the remainder (and modify it) using access/sql... anyone have a
direction for me?

 

[Allen Browne]

Could you use the Round() function in Access?

For example, if you have:
[Amount] * 1.17
use:
Round([Amount] * 1.17, 2)

Or perhaps:
CCur(Nz(Round([Amount] * 1.17, 2), 0))

 

[Lord Madrid]

The Round() function isn't accurate enuf... and I need to make sure and
always round UP.

Example: given the number 1.52001, it needs to be rounded up to 1.53.

I've even gone as far as adding +.00444 to the base number, then use
the round() function.... it will work in all cases, except where the
base number has 0's in the middle (like in the example) Thus, the
only way I can see doing this, is to read the remainder, if it's longer
than 2 (contains 3+ digits), add +.01.

Could you use the Round() function in Access?

For example, if you have:
[Amount] * 1.17
use:
Round([Amount] * 1.17, 2)

Or perhaps:
CCur(Nz(Round([Amount] * 1.17, 2), 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Something that seems easy, but I'm having a hard time figuring it out
for some reason...
For a rounding issue at work, we need to round up to the penny (out
from 5 decimal places). The easist way I can think of, would be to read
the remainder, and if it's greater than 3, add .01. I can't find a way
to read the remainder (and modify it) using access/sql... anyone have a
direction for me?

 

[Guest]

Something that might work, in theory (I haven't tried it out myself):

Int() function is supposed to round down, possibly to the specified number
of decimals in the same fashion that Round() does; if so, perhaps set the
function to round all your values down, then immediately add .01?

Hope this helps.

:
....

 

[Guest]

Do me a favor, if it does work, and let me know how you are using it? I'm
afraid I couldn't figure out how to test it in a sample, and I'm curious.

 

[Lord Madrid]

Int() is exactly the opposite of what I need... instead of pulling the
integer, I need something that returns the decimal numbers... This
sounds like it should be so easy...

For example:
Int (210.67) would return 210
Int (2.98) would return 2

I need something that:
(210.67) would return 67
(2.98784) would return 98784

 

[Guest]

Maybe this is a round-about way of doing it, but what about an expression
that first multiplies the value by 100, then runs the Int() function, and
divides the result by 100?

 

[Guest]

Sorry, to amend, it would multiply by 100, run Int(), *add 1*, then divide by
100.


18.52278 * 100 = 1852.278, Int() = 1852 + 1 = 1853 / 100 = 18.53

 

[Allen Browne]

How about:
-Int(- 100 * [Amount]) / 100

Multiply by 100.
Negate.
Int() now rounds down to the nearest penny.
Negate again.

The double-negation has the effect of rounding in the opposite direction to
what Int() normally does.

 

[Lord Madrid]

Not quite it, but I've got you thinking
Given the example above (1.52001), this needs to round UP to 1.53
(because of the .00001).
You can't always add .01 to all amounts, incase it comes out to an even
penny amount (2.50), it would round up a penny when it shouldn't

Still thinking I'll have to read the length of remainder, if it's
greater than 2, then add .01... just don't know how...

How about:
-Int(- 100 * [Amount]) / 100

Multiply by 100.
Negate.
Int() now rounds down to the nearest penny.
Negate again.

The double-negation has the effect of rounding in the opposite direction to
what Int() normally does.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Int() is exactly the opposite of what I need... instead of pulling the
integer, I need something that returns the decimal numbers... This
sounds like it should be so easy...

For example:
Int (210.67) would return 210
Int (2.98) would return 2

I need something that:
(210.67) would return 67
(2.98784) would return 98784

 

[Allen Browne]

I guess I'm missing something here.

When I open the Immediate Window (Ctrl+G) and enter:
? -Int(- 100 * 1.52001) / 100
on my computer Access respond with:
1.53

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Not quite it, but I've got you thinking
Given the example above (1.52001), this needs to round UP to 1.53
(because of the .00001).
You can't always add .01 to all amounts, incase it comes out to an even
penny amount (2.50), it would round up a penny when it shouldn't

Still thinking I'll have to read the length of remainder, if it's
greater than 2, then add .01... just don't know how...

How about:
-Int(- 100 * [Amount]) / 100

Multiply by 100.
Negate.
Int() now rounds down to the nearest penny.
Negate again.

The double-negation has the effect of rounding in the opposite direction
to
what Int() normally does.

Int() is exactly the opposite of what I need... instead of pulling the
integer, I need something that returns the decimal numbers... This
sounds like it should be so easy...

For example:
Int (210.67) would return 210
Int (2.98) would return 2

I need something that:
(210.67) would return 67
(2.98784) would return 98784

Lea wrote:
Something that might work, in theory (I haven't tried it out myself):

Int() function is supposed to round down, possibly to the specified
number
of decimals in the same fashion that Round() does; if so, perhaps set
the
function to round all your values down, then immediately add .01?

Hope this helps.

"Lord Madrid" wrote:

 

[Lord Madrid]

Take it step by step... you must have something else configured to get
that value...
int(100*1.52001) = 152 (001 rounds down)
152/100 = 1.52
tested & functioning this way...

What's your trick Allen

I guess I'm missing something here.

When I open the Immediate Window (Ctrl+G) and enter:
? -Int(- 100 * 1.52001) / 100
on my computer Access respond with:
1.53

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Not quite it, but I've got you thinking
Given the example above (1.52001), this needs to round UP to 1.53
(because of the .00001).
You can't always add .01 to all amounts, incase it comes out to an even
penny amount (2.50), it would round up a penny when it shouldn't

Still thinking I'll have to read the length of remainder, if it's
greater than 2, then add .01... just don't know how...

How about:
-Int(- 100 * [Amount]) / 100

Multiply by 100.
Negate.
Int() now rounds down to the nearest penny.
Negate again.

The double-negation has the effect of rounding in the opposite direction
to
what Int() normally does.

Int() is exactly the opposite of what I need... instead of pulling the
integer, I need something that returns the decimal numbers... This
sounds like it should be so easy...

For example:
Int (210.67) would return 210
Int (2.98) would return 2

I need something that:
(210.67) would return 67
(2.98784) would return 98784

Lea wrote:
Something that might work, in theory (I haven't tried it out myself):

Int() function is supposed to round down, possibly to the specified
number
of decimals in the same fashion that Round() does; if so, perhaps set
the
function to round all your values down, then immediately add .01?

Hope this helps.

"Lord Madrid" wrote:

 

[John Spencer]

The trick is the way int handles negative numbers

Int(-100*1.52001) will return -153
divide that by 100 returns -1.53
Reverse the sign by placing - in front of the int function returns 1.53

In the case of a negative number argument, the Int function returns the
first negative integer less than or equal to the number.

Take it step by step... you must have something else configured to get
that value...
int(100*1.52001) = 152 (001 rounds down)
152/100 = 1.52
tested & functioning this way...

What's your trick Allen

I guess I'm missing something here.

When I open the Immediate Window (Ctrl+G) and enter:
? -Int(- 100 * 1.52001) / 100
on my computer Access respond with:
1.53

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Not quite it, but I've got you thinking
Given the example above (1.52001), this needs to round UP to 1.53
(because of the .00001).
You can't always add .01 to all amounts, incase it comes out to an even
penny amount (2.50), it would round up a penny when it shouldn't

Still thinking I'll have to read the length of remainder, if it's
greater than 2, then add .01... just don't know how...

Allen Browne wrote:
How about:
-Int(- 100 * [Amount]) / 100

Multiply by 100.
Negate.
Int() now rounds down to the nearest penny.
Negate again.

The double-negation has the effect of rounding in the opposite
direction
to
what Int() normally does.

Int() is exactly the opposite of what I need... instead of pulling
the
integer, I need something that returns the decimal numbers... This
sounds like it should be so easy...

For example:
Int (210.67) would return 210
Int (2.98) would return 2

I need something that:
(210.67) would return 67
(2.98784) would return 98784

Lea wrote:
Something that might work, in theory (I haven't tried it out
myself):

Int() function is supposed to round down, possibly to the specified
number
of decimals in the same fashion that Round() does; if so, perhaps
set
the
function to round all your values down, then immediately add .01?

Hope this helps.

"Lord Madrid" wrote:

 

[Lord Madrid]

Ah ha! That makes sense now... thank you for the explination. I'm in
the process of testing multiple amounts, but it's looking good (cross
fingers). Thank you all for the help!

The trick is the way int handles negative numbers

Int(-100*1.52001) will return -153
divide that by 100 returns -1.53
Reverse the sign by placing - in front of the int function returns 1.53

In the case of a negative number argument, the Int function returns the
first negative integer less than or equal to the number.

Take it step by step... you must have something else configured to get
that value...
int(100*1.52001) = 152 (001 rounds down)
152/100 = 1.52
tested & functioning this way...

What's your trick Allen

I guess I'm missing something here.

When I open the Immediate Window (Ctrl+G) and enter:
? -Int(- 100 * 1.52001) / 100
on my computer Access respond with:
1.53

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Not quite it, but I've got you thinking
Given the example above (1.52001), this needs to round UP to 1.53
(because of the .00001).
You can't always add .01 to all amounts, incase it comes out to an even
penny amount (2.50), it would round up a penny when it shouldn't

Still thinking I'll have to read the length of remainder, if it's
greater than 2, then add .01... just don't know how...

Allen Browne wrote:
How about:
-Int(- 100 * [Amount]) / 100

Multiply by 100.
Negate.
Int() now rounds down to the nearest penny.
Negate again.

The double-negation has the effect of rounding in the opposite
direction
to
what Int() normally does.

Int() is exactly the opposite of what I need... instead of pulling
the
integer, I need something that returns the decimal numbers... This
sounds like it should be so easy...

For example:
Int (210.67) would return 210
Int (2.98) would return 2

I need something that:
(210.67) would return 67
(2.98784) would return 98784

Lea wrote:
Something that might work, in theory (I haven't tried it out
myself):

Int() function is supposed to round down, possibly to the specified
number
of decimals in the same fashion that Round() does; if so, perhaps
set
the
function to round all your values down, then immediately add .01?

Hope this helps.

"Lord Madrid" wrote: