Round Function (1 Viewer)

aliikhlaq2006 · Aug 10, 2017

I want to round my figure in Query. Let say i have a figure 23,006 and want to round it up to 23,010. and if its 23,005 or lower to 23,000.
I am using this expression "Round([table:Column],-1) and in result i got error #func!

plog · Aug 10, 2017

Here's the documentation for the Round function:

https://www.techonthenet.com/access/functions/numeric/round.php

Also, when you need to reference a table and column you do it like so:

.[Column]

Brackets and a dot, not colon.

MarkK · Aug 10, 2017

Also, to round to numbers greater than one, you need to write your own function, like...

Code:

Function RoundToNearest(Number As Double, RoundTo As Double) As Double
   RoundToNearest = CLng(Number / RoundTo) * RoundTo
End Function

Also, it would be faster to just do that math in a query, if that's your end goal...

Code:

RoundedTo10Field: CLng(FieldToRound / 10) * 10

hth
Mark

aliikhlaq2006 · Aug 10, 2017

plog said:
Here's the documentation for the Round function:

Also, when you need to reference a table and column you do it like so:

.[Column]

Brackets and a dot, not colon.

Sorry i type wrong its dot

aliikhlaq2006 · Aug 10, 2017

MarkK said:
Also, to round to numbers greater than one, you need to write your own function, like...

Code:

Function RoundToNearest(Number As Double, RoundTo As Double) As Double RoundToNearest = CLng(Number / RoundTo) * RoundTo End Function

Also, it would be faster to just do that math in a query, if that's your end goal...

Code:

RoundedTo10Field: CLng(FieldToRound / 10) * 10

hth
Mark

I am basically totally a new in access and just testing my skills i not understand the 1st solution but the 2nd one work for me. Thank you so much

micks55 · Aug 12, 2017

Glad you found a way but here's an alternative which I prefer because it's just good old math.

If you want to round 23006 Up but round 23004 Down then the Int() function will do it. You would use it like this: =INT(((myNumber+5)/10))*10).

If myNumber is 23006 it will be rounded UP to 23010.
If myNumber is 23004 it will be rounded DOWNto 23000.

Here's what happens, innermost () first.

myNumber = 23006
a. Add 5 to myNumber (23011)
b. Divide it by 10 (23011/10=2301.1)
c. Get the Integer (2301)
d. Multiply by 10 (2301x10=23010)

myNumber = 23004
a. Add 5 to myNumber (23009)
b. Divide it by 10 (23009/10=2300.9)
c. Get the Integer (2300)
d. Multiply by 10 (2300x10=23000)

Round Function (1 Viewer)

aliikhlaq2006

Registered User.

plog

Banishment Pending

MarkK

bit cruncher

aliikhlaq2006

Registered User.

aliikhlaq2006

Registered User.

micks55

Registered User.

Similar threads

Users who are viewing this thread