Access 97 Imperfect Rounding Equations

[sunnydanny]

I have tried many ideas given to me. None work.
For example.
=(5*.025)*100)/100 will show 12 instead of 13
=(5*.025)*100+.05)/100 will show 13 but when an imput of 0 is placed where the 5 is I end up with a .01 which is incorrect as it should be 0.

Can anyone please help this poor beginner solve this problem simplistically?

 

[MichaelRed]

perhaps we use a different number base, but I do not see how to even come close to the 'expected' results





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[TonyJollans]

Hi sunnydanny,

Even with correct parentheses Michael is right, but what you have stumbled across is Access rounding which round .5 down where everybody else rounds it up. A possible workaround is to use:

=Format(((5 * 0.025) * 100) / 100, "0.00")

which will I think give you 0.13

Enjoy,
Tony

 

[sunnydanny]

Is there any way to do this in Visual Basic? Telling it that an imput of 0 will not require a rounding function? If so can someone walk me through it. I don't know how to write VB. Thanks.

 

[TonyJollans]

Hi sunnydanny,

Do you mean VB or VBA (which is the language 'within' Access)? But before we get into code, where do you want the result to appear and where are the input(s) coming from?

Enjoy,
Tony

 

[sunnydanny]

I would assume it's VBA Tony. It's in access I'm asking about. And the results I'd like to appear on a form or report. Either one. The inputs come from a query I run. Basically what I'm asking it to do is take a sum(total of various imputs from a table) from a query and multiply them by a number (for example: .025), round that answer and then take the rounded amount and add it to other rounded answers to form a total on that form/report. Is this possible? Thanks again for your continued help.

Danny.

 

[TonyJollans]

Hi sunnydanny,

If your values are in a Query and your Form (or Report) is based on that Query then it is straightforward to output the rounded amount in a control and to put a total of the rounded amounts in the footer.

The only thing to watch out for (as you have already discovered) is Access rounding. Almost every other piece of software follows 'standard' rounding rules - i.e. add 5 one place to the right of the reqired significance and truncate. Access tries to compensate for the case where a lot of values end in 5 and all get rounded up by taking into account some other digits - I don't know the exact rules it uses - and all it manages to really achieve is total confusion.

You can live with Access rounding if it satisfies your requirements; if not then you must manage it yourself as you tried and posted to start with - all you got 'wrong' was that you should have added a smaller amount - 0.005 or, better, 0.001 - and there is the problem with doing it yourself - it is error prone. If you want to be adventurous I think you can invoke the Excel rounding function from within Access but I have never done it.

Enjoy,
Tony

 

[Scanman999]

Hi all,

I've been trying allsort of calculation tricks like the ones I found all around this site but always came up with a number that wouldn't round properly.

So go and take a look at my FAQ "How to work around the ROUNDing "problem" in Access ?" where you'll find my own rounding routine that should fit your needs.

Enjoy.
Scanman

 

[davemcdonaldireland]

For a full explanation of why Access rounds differently and a way around if, see my FAQ:

The Round() function rounds 2.45 to 2.4 and is correct. Why?

http://www.tek-tips.com/faqs.cfm?spid=705&sfid=3734

Dave Mc Donald

http://www.mcdonaldit.com

 

[JimHorton]

When rounding is that important NEVER USE FLOATS.

Use scaled integers, like Currency type (a scaled 64-bit integer) or scale a long (32-bit integer) yourself and do the calcs, then scale back down.

I can tell you from the real world that's how it's done--I worked on an extremely mission critical large banking application and we did not use the dbms's Float types for this work.
--jim