Access rounds down at a "5" and not up.

[mtairhead]

I have a query that uses the round function as follows:

Round([LoanAmt]*0.0125,2)

This should multiply that LOANAMT field by 1.25%, and round that answer to two decimal places.

1.776 rounds to 1.78 - That's good
1.774 rounds to 1.77 - That's good

BUT

1.775 rounds to 1.77 - Why? Didn't we learn in school to round up on a five, not down? Can I change this setting, if it is one?

Andrew

 

[FoFa]

try this:
Round(([LoanAmt]*0.0125),2)

 

[mtairhead]

No such luck...

Thanks, though.

Has anyone had this issue? It just doesn't make sense (or cents).

Andrew

 

[neileg]

See this thread for a link from Pat.
http://www.access-programmers.co.uk/forums/showthread.php?t=85431&highlight=access+math

 

[mtairhead]

Good Article

I'm not sure it addresses my problem - This is event more basic than some of the issues Luke Chung has brought up. Rounding down at five (From 1-5) and up from 6-9? Is there a profession on earth that does this?

At any rate, I think I've found a solution based on this thread.

My problem is that Access will round numbers like 1.745 to 1.74 and not 1.75, as we learned to do in grade school. If I add .005 to each value, before I have it round, the number will be correct. (Thank you gecko_1!)

1.74 + .005 = 1.745, which rounds down to the original 1.74
1.749 + .005 = 1.754, which rounds to 1.75
1.745 + .005 = 1.75, which is already correctly rounded to two decimal places

I'd appreciate some positive/negative feedback on this - I need to know if I can count on the "fix" to do its work well, and a fresh mathematical/logical mind would help.

Andrew

 

[Pat Hartman]

The Access rounding routine is more sophisticated than what we were taught at school. Since 1-4 (four numbers) always round down, there is a slight upward weight because 5-9 (five numbers) always round up. To even this out, Access rounds 5 up when the result will be even and down when it will be odd.

If you always want .05 to round up, adding .05 and truncating the result will solve your problem. The only flaw with this is that you need to be aware of how many decimals you are actually rounding to.

 

[Cosmos75]

I suggest that you create your own VBA rounding function, to use like you would Round(). It isn't very hard to do, see here for an example.