Rounding to nearest hundred

Not sure what happened to my original post but here it goes again......

I have a calculation that I need to perform in a query. Before I do the calculation however I need to round down numbers to the nearest hundred so a number like 1485.45 would be 1400, something like 543 would be 500 and something like 68 would be 0.

In excel this is easy for me to do using the following formula/function:


Where cell C2 might contain something like 588 the net result is 500.

How can I acheive this in Access (query)? There doesn't appear to be a rounddown function nor a len function in access.

Any help is appreciated!

M Opine

Post your answer or comment

comments powered by Disqus
I have been working on a query and I need to round up to the nearest 1000. Example, an employee's Benefit Salary is 24950.00 and I need to round to nearest 1000 so I can determine the insurance pay out, so I want to see in my query the salary rounded to 25000.00. I have tried help and it just talks about rounding after the decimal. In my Access 97 bible it doesn't even address this logic it only explains how to round fractions.

I know this must be able common in the HR world, so why can't I find anything on this type of logic.

I have a rounding problem that I cannot seem to hack through.
99% of the time the entry in a field (which has to be calculated) is either an integer or a precise half - i.e. 10 or 10.5 for instance.
Occasionally the computation goes to 10.0001 or 10.49998 where the environment has peculiarities.
I need to round either to an integer or to a half, but it isn't as simple as rounding up or rounding down.
If the value is between 10.750 and 11.250 I would want to round to 11 but if it was between 11.251 and 11.749 I would want to round to 11.5.
The issue is not fixed to these numbers since the whole number could be anything.

I am currently using

	    Me.txtNumRound = (Int(Me.txtNum * 2)) / 2

and this rounds down to the half. If I change the 2 to -2 it rounds up to the half. If I change the 2 to 4 it does the same but to the quarter.

I think I need a sort of if test which says if the value after the decimal point is between x and y then round down to the nearest half or if between a and b then round up to the nearest half.
But I might be trying to create a sledge hammer to crack a nut.
Any suggestions would be most welcome - and yes I know you shouldn't store a calculated field but in this instance there are very good reasons why!
Thanks in anticipation
Best wishes

Hi folks,

I'm trying to build a pro-rata annual leave calculator based on the starting date of an employee.

I have it working but it's rounding the result to the nearest whole day. HR have asked that it calculates to the nearest half day.

By way of an example, an employee who started earlier this year had a pro-rata annual leave allowance of 20.6 days. This is currently being rounded to 21 days whereas HR would prefer it to round to 20.5 days.

Does anyone have a function that would work here?



Is there a way to round a whole number the nearest multiple of 5, ie. 27 rounds to 30 or 12 rounds to 10. I know how to do it in excel but tried it in access with no success. Any help provided will be appreciated.

This has me stumped so far - I need to round up to the nearest nickel, preferably in a query. Have figured out how to round to the nearest nickel, but cannot get it to round up.

I round to the nearest nickel like this:
RoundedNumber: Round(20*[MyField],0)/20

Any ideas?

Does anyone have a way to round up or down to nearest 100 and 500.

43746 = 43500
8763 =8800




I'm creating a stock tracking database. I need to enter weekly rental charges such as £1.35.

When i enter £1.35 it is rounding it to £1.00

If i enter anything over £1.50 then it rounds to £2.00

I need it to show the exact amount, how do I do this.


Hi All,
I use the following generally available function for rounding.
I thought it cured the problem with 'Bankers' rounding which is Access' default method of rounding.
But can someone tell me why, when using this function on the value 4.725 it returns 4.72 when it should return 4.73?
I am using it in this instance within a query to round off (to the nearest) a sales tax (vat) calc. ie:
vbaRound(Abs(qryInvoiceExport02.NetAmount)*0.175,2 )
As Im not specifying a parameter to round up or down the function will by default round off.
I've broken down the single line of code into constituent parts to see whats happening and what happens is the "Int(dblValue)" on the 3rd line contains what I expect (473) but after execution vbaRound contains 4.72!!

Am I missing something obvious here?

	Public Function vbaRound(dblValue As Double, intDecimals As Integer, _
                         Optional RoundOpt As enumRoundOpt = rNearest) As Double
'Public Function vbaRound(dblValue As Double, intDecimals As Integer,

' PURPOSE: To replace VBA's Round() function
' 1) dblValue - Value to Round
' 2) intDecimals - Decimal places to round to (any positive integer)
' 3) enumRoundOpt - To specify whether to round to nearest, up or down.
'    a) Valid argument values are
'       i)   rNearest (equivalent to 0)
'       ii)  rUp      (equivalent to 1)
'       iii) rDown    (equivalent to 2)
'       iv)  Any other number results in rounding to the nearest
' Result is a double value.
' e.g.
' vbaRound(12.3456, 2, rNearest)   = 12.35
' vbaRound(12.3456, 2, rUp)        = 12.35
' vbaRound(12.3456, 2, rDown)      = 12.34
' - http://AccessDB.Info

On Error GoTo HandleErr:

Dim dblPlacesFactor As Double
Dim dlbRoundFactor As Double
Dim dblCalc As Double

dblPlacesFactor = 10 ^ intDecimals

Select Case RoundOpt
Case rNearest    'Round to Nearest
    dlbRoundFactor = 0.5
Case rUp         'Round UP
    dlbRoundFactor = 1
Case rDown       'Round DOWN
    dlbRoundFactor = 0
Case Else        'Round to Nearest by default for all other values
    dlbRoundFactor = 0.5
End Select

'Calculate rounded value & Return value
'Original code:
'vbaRound = Int((dblValue * dblPlacesFactor) + dlbRoundFactor) / dblPlacesFactor

'Code broken down into component parts:
dblValue = dblValue * dblPlacesFactor
dblValue = dblValue + dlbRoundFactor
vbaRound = Int(dblValue) / dblPlacesFactor

Exit Function
'Leave value by default alone if an error occurs
'OR set it to any value you prefer (e.g. 0)
vbaRound = dblValue
Resume ExitHere:
End Function

I am trying to import a .csv file which has several numeric fields containing values with decimal places.

When I import the file using:

-"Get External Data / Import" (without using a SPEC)

The decimals are imported into the table correctly

-"Get External Data / Import" (using a SPEC)

Values are rounded to nearest whole number on import

-"Transfer Text / Import Delimited" within a Macro (using a SPEC)

Values are rounded to nearest whole number on import

Within the table my value field is set up as follows:

Field Size: Double
Decimal Places: Auto
All other criteria: left as deault

As I have several files like this which I will need to import on a regular basis I would like to incorporate it within a macro

Any advice on how to achieve the import of values (with decimals) would be appreciated.

I'm using this query:
60_days_from_f2f: DateAdd("yyyy",1,[Initial_Visit_1stF2F])-60

I'm adding 1 year, then subtracting 60 days. If the new date falls on a weekend I need to change the date to the upcoming monday, which would be either 1 or 2 days forward.

How can I make the date round to Monday if it falls on a weekend?

Any idea how to calculate this?

[RegDate] plus 28 years. Right now I can do that with this:


This gives me : [ExpDate] = 2/15/1950 + 28 years = 2/15/1978

I'm trying to get the date to round to the end of that calendar year (12/31/1978)

[ExpDate] = 2/15/1950 + 28 years = 12/31/1978

Any help would be great!


I need the cr value to be the washup value divided by 4 rounded down to the nearest whole number.

For example, if washups is less than 4 then cr should be 0, if washups is 5-8 then cr =2.

Here's my calculation
Cr = Int(Washups / 4)


Is there a simple way of getting access to round the time to the nearest 5 or 10 minutes


I'm working with queries in Access97, and would like my query to round a number to the nearest million.

So, if I have 1,233,555, I'd want it to round down to 1,000,000
And if I have 1,544,121, I'd want it to round up to 2,000,000

Anything under 500,000 would round down to 0.

It'd driving me up the wall! The answer is probably so simple, and it's stumping me!

Thanks for all your help!

I have got a formula in a Query as follows:

Suggested Qty: [StockQty]/[Numerator]

How do I round this down to the nearest 1?

(i.e 7.2 = 7 or 9.9 = 9)

Any help would be greatly received.



I have been searching the forum for the last hour or so. I'm new to access, vba and macros. So I asking for some help, please be gentle with me. I'm am trying to round up the the nearest twenty.

example. I have a table that contains an estimate take-off f and I want to round the pipe footage to the nearest 20 for purchase amounts and update the table.


Item QTY
Pipe 534.08
Pipe 405.5
Pipe 28.83
Pipe 1381

Item QTY
Pipe 540
Pipe 420
Pipe 40
Pipe 1400

Another problem I'm not sure how to fix is, some of the qty totals are two decimals and others are nine. Maybe not an issue but thought it might be.

So if anyone is compelled to help a greenie out it would be much appreciated.


Hello all,

I'm hoping someone out there can be very kind and help me please!!

I have a field [Duration] which contains a numeric value which relates to seconds that a call has lasted for. So, typical values can be


Any value really, but they are all seconds.

I have another field in the same tables [Duration_Minutes]. What I would like to do is take the value of [Duration] and round it UP to the nearest minute, so in the examples above:

0 (seconds) would still be 0 (minutes)
2 (seconds) - would become 1 (minute)
157 (seconds) would become 3 (minutes)

etc etc etc. The simple calculation in my simple brain is obviously, seconds divided by 60 to get the minutes, but I don't know how to round it UP (always UP, no matter how close it is to the lower minute).

Any help greatly appreciated

I have values for example like, 0.03, 1.14, 1.28 I'd like to round them to the nearest tenth. So resulting values will be as follows:

0.03 = 0.00
1.14 = 1.10
1.28 = 1.30

How do i achieve this? What's the formula?

I have a field in a query that I want to round up to the nearest whole number. How do I do this? Here is the field:

Numb: [MonTimesEmpPay]

I am designing a querry and have the following formula at the top of the field

Total 1/1:[1/1]*[1/1v]

But I want the answer to be rounded to the nearest 100. Say the answer is 156 I want it to round to 200 or if it is 145 I want it to round to 100. I have wore out my children looking for an answer but no answers. If you could just write me the proper formula including my formula at the top I might be able to duplicat it.


In access2000 I need to round up to nearest whole number.
Here's an example: 1.00001 would be 2.

Anyone know how to do this?


Is there an SQL function which will round down a time like 11:56:18 to 11:45 for example?

Round(20 * [MyField], 0) / 20

MyField = 733.20 - I want my value to be 733.25

I have a field that sums pounds. I need to convert this to tons (rounded to the nearest 3 decimals), then multiplied by $67.50. This will calculate a fee payment.

This is what I have now:


The total pounds is 2675.
After dividing by 2000, Access generates a number of 1.3375. It rounds 1.3375 to 1.337 which generates an incorrect final total. Not sure how to alter this to round properly.


Not finding an answer? Try a Google search.