Access 97, rounding currency to two decimals in vb code.

[Bob]

Access 97 does not have the Round function available. I need to take a
result calculated in code that comes out to 4 decimals and round it to two
decimals before storing in the table. Can anyone give me sample bit of of
code that does this in 97? Not that I'm old fashioned, the guy I'm doing
this for is he does not want to develop in 2003.

Thanks for any help,
Bob

 

[Steve Schapel]

Bob,

Try it like this...
Int([YourNumber]*100+0.5)/100

 

[Bob Richardson]

I don't think that will work on negative numbers, if that's a consideration.

Bob,

Try it like this...
Int([YourNumber]*100+0.5)/100

--
Steve Schapel, Microsoft Access MVP

Access 97 does not have the Round function available. I need to take a
result calculated in code that comes out to 4 decimals and round it to
two decimals before storing in the table. Can anyone give me sample bit
of of code that does this in 97? Not that I'm old fashioned, the guy I'm
doing this for is he does not want to develop in 2003.

Thanks for any help,
Bob

 

[Bob]

Thanks, I'll give it a try.
For negative numbers I can always use an if statement to determine signs and
subtract the .5
I think that would work.
Bob

I don't think that will work on negative numbers, if that's a
consideration.

Bob,

Try it like this...
Int([YourNumber]*100+0.5)/100

--
Steve Schapel, Microsoft Access MVP

Access 97 does not have the Round function available. I need to take a
result calculated in code that comes out to 4 decimals and round it to
two decimals before storing in the table. Can anyone give me sample bit
of of code that does this in 97? Not that I'm old fashioned, the guy I'm
doing this for is he does not want to develop in 2003.

Thanks for any help,
Bob

 

[Steve Schapel]

Bob,

I don't agree with Bob Richardson here. I think the expression I gave
will be fine for negative numbers, just as it is , without any need for
If statements or whatever. At least, it will give the same result as
the Round function. If you need it to be different from this, for some
reason, then the use of the Sgn() function may be applicable.

 

[Bob]

Thanks to both of you, you've really helped.
I did try it with the If statement for negative numbers and that does give
me the result I wanted.

The idea is for instance if I write a cheque for which there are two tax
amounts calculated (all positive, all is ok). But later I want to reverse
the cheque by entering a negative amount and then recaculating the taxes so
that they are exactly the same as for the positive amount, but now negative.
The if statement approach did allow me to do precisely this.
I haven't tested without the if statement, but what the heck, it works !

Thanks again to you both
Bob

 

[peregenem]

The idea is for instance if I write a cheque for which there are two tax
amounts calculated

You may need to consider Banker's Rounding:

"When you add rounded values together, always rounding .5 in the same
direction results in a bias that grows with the more numbers you add
together. One way to minimize the bias is with banker's rounding.

"Banker's rounding rounds .5 up sometimes and down sometimes. The
convention is to round to the nearest even number, so that both 1.5 and
2.5 round to 2, and 3.5 and 4.5 both round to 4."

How To Implement Custom Rounding Procedures:
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652#XSLTH3170121122120121120120