Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Queries and JET SQL FAQ

Using Functions in Queries

Rounding Number in a Query or Module by PaulBricker
Posted: 6 Feb 03 (Edited 12 Jan 07)

Rounding values can be a daunting task.  If you have Access 2000 or newer, you have the Round Function you can use for many of your calculations.  If you are using Access 97 or have calculations that don't fit in the Round Function format you can try one of these to accomplish what you need. Edited 1/11/07. to adjust for floating point issues with VB6.3  
Place them in a new column in your query and replace myField with the name of your Numeric Field.

To round to 1 decimal place
myVal:Int(CDec((myField + 0.05) * 100)) / 100
123.45 returns 123.5
123.44 returns 123.4

To round to 2 decimal places
123.456 returns 123.46
123.454 returns 123.45

To round to the nearest ONE digit
myVal:Int(myField + .5)
123.55 returns 124
123.45 returns 123

To round to the nearest TEN digit
myVal:Int(((Int(myField) / 100) + 0.05) * 10) * 10
128.45 returns 130
123.45 returns 120

To round to the HIGHER TEN digit
myVal:Int((((myField + 0.999) / 100) + 0.09) * 10) * 10
1.01 returns 10
10 returns 10
10.01 returns 20

The alias myVal can be replaced by any text value you want for a field name.  

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close