Rounding to nearest 0.5 1

[cpmasesa]

Hi All,

I need to round a number to nearest 0.5 Round() allows me to round to x significant digits. I want it to the nearest 0.5

Any ideas??

cpmasesa

 

[Dericki]

try this

SELECT Table1.amount, round([amount],0.5) AS Expr1
FROM Table1;

Good Luck

 

[cpmasesa]

Does not work! rounds to nearest whole number. Round([Amount],2) rounds to two decimal places but i want it to be nearest 0.5.

Maybe i just have to settlefor 2 decimal places??!

TAW

 

[scriverb]

Give this Switch statement a try:

Switch((Me.RawNumber - CInt(Me.RawNumber - 0.5)) >= 0.75, CInt(Me.RawNumber - 0.5) + 1, (Me.RawNumber - (CInt(Me.RawNumber - 0.5))) >= 0.25, CInt(Me.RawNumber - 0.5) + 0.5, True, CInt(Me.RawNumber))

Change Me.RawNumber to your field, variable or control that holds the number to be rounded to .5.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[cpmasesa]

Bob,

Thanks! It looks scary but works fine! Modified it a little to suit my 'rounding regime'.

Thanks

Clemens

 

[scriverb]

Great!!! Thanks for the Star.

What did you modify and why? Is your rounding regime something other than posted because it worked in all instances to round up to the next whole number if .75 or greater and up to the .5 if .25 or greater. All others dropped back to the .5 and the next lowest whole number.

Just curious. Repost actually what you used and your required rules if you have a moment. Thanks

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[cpmasesa]

Hi Bob,

Was over enthusiastic! Changed back to your posted solution as i found my 'supposed' regime was erronous!

Thanks again.

BTW, Where can i get info on the 'Switch' and 'CInt' functions that you use? Nothing in the help files

TIA

Clemens

 

[scriverb]

They should both be in the Help files. They are both functions. I have copied a portion for you here:
SWITCH FUNCTION
Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.
Syntax

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

The Switch function syntax has these parts:
Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding expression is True.

CInt and CLng Functions
CInt Integer -32,768 to 32,767; fractions are rounded.
CLng Long -2,147,483,648 to 2,147,483,647; fractions are rounded.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[cpmasesa]

Thank you Bob.