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:

=ROUNDDOWN(C2,1-LEN(INT(C2)))

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

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:

=ROUNDDOWN(C2,1-LEN(INT(C2)))

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

- Rounding to nearest whole number
- Rounding to nearest half
- Rounding to the nearest .5
- Rounding to the nearest multiple of 5
- Round - UP - To Nearest nickel
- Rounding to 100 or 500
- Currency rounding up to nearest whole number
- Yet another rounding issue..
- Problem trying to Import decimals
- Round to Monday?
- Calculate Date & Round to the end of the calendar year?
- Round down to nearest whole number
- Rounding to the nearest X Minutes
- Rounding to the nearest million
- Rounding Down to Nearest 1
- Help with rounding up to nearest 20
- Round Seconds Up To Nearest Minute
- Round to the nearest tenth
- Round number up to nearest whole number?
- How to round a querry
- Round up to next whole number
- Round Down Time to nearest 15min
- Round up to nearest nickel
- Rounding a calculated field

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

Code: Me.txtNumRound = (Int(Me.txtNum * 2)) / 2and 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

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?

Thanks,

Dave

I round to the nearest nickel like this:

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

Any ideas?

43746 = 43500

8763 =8800

etc.

Mike

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.

Thanks

Ian

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?

Thanks.

Code: 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 ' ' AUTHOR: ' ' ARGUMENTS: ' 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 ' ' RETURNS: ' 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 ExitHere: Exit Function HandleErr: '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

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.

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?

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

Code: DateAdd("yyyy",28,[RegDate])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)

Ex.

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

Any help would be great!

Thanks!

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)

Hi,

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

Thanks

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

Thanks

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!

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.

Regards

Carly

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.

ie.

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.

Thanks

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

0

2

157

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

0.03 = 0.00

1.14 = 1.10

1.28 = 1.30

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

Numb: [MonTimesEmpPay]

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.

dan

Here's an example: 1.00001 would be 2.

Anyone know how to do this?

Thanks.

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

This is what I have now:

Code: =Round(Sum([Hazardous_Waste]/2000*67.5),3)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.

Thanks.

Not finding an answer? Try a Google search.