Rounding to the nearest 10

[zenenigma]

I've got thousands of amounts to work with in my table. The amounts vary anywhere from $100.00 to $500,000. I'd like to round each amount to the nearest $10 ($101.50 would be $100.00, $1257.52 would be $1260.00). The round function has been no help because it will only round to the nearest whole number or decimal. I've tried using left([amount],(len([amount])-x)), etc. but I'm also having no luck because the different lengths show different results, and $10000.00 (and similar even numbers) has a length of 5, rather then 8.

Any help would be appreciated, even if it's a procedure.

Rounding isn't absolutely necessary. For what I need I could also make everything right of the 10's position = 0.00 ($1234.62 -> $1230.00)

-ZE

 

[zenenigma]

I've just (sort of) fixed my problem, though it's not a great way of doing it. First I made a new field called "AlteredAmt" in my table and set it to Currency w/ 3 decimal places. I then ran an update query, updating the "AlteredAmt" field with "OriginalAmt" + 0.001 (thus giving it the full length, reguardless of if the number ends in 0's. From there, it was only a matter of taking the left digits of each value (using length) and appending "0.00" to the end.

Very Ghetto, and I'm still hoping there's a better way to do these kinds of roundings in the future.

Any help is still very appreciated

-ZE

 

[GummowN]

Try using the int function:
y = int(x/10)*10 gives for
x = 13 y = 10
x = 17 y = 10
x = 1034 y = 1030
To round up you have to be a bit more bizarre.

If mid(Right("0000000000000" & (x*100),13),11,1) >= 5 then
y = int((x/10)+1)*10
Else
y = int(X/10)*10
End if

Hope that helps

 

[PaulBricker]

This should return the values you need rounded properly.

MyAmount:Int(((Int([Amount])/100) +.05)*10)*10

Paul