Rounding to the nearest 1000 in Access query

[emik]

I found a post here that asked a similiar question but had no replies. I need to round currency values in my query to the nearest 1000.

I have a calculated field and the value is:

$10,847.81 so I would like to see a value of $11.

So does anyone know how to round to the nearest 1000 ?

Thank you.

 

[Golom]

? val(Format(10847.81,"#,"))
11

 

[emik]

Perfect thanks!

My end result:

Bonds: Val(Format(Sum(IIf([GL_Group]="Bonds",[BookValue],0)),"#,"))

 

[emik]

One last question, can I ask how it knows to round to the nearest 1000 ? I'm guessing it's finding the last , and returning the value before that. But let's say I wanted to round to the nearest 100, how would this change?

 

[CautionMP]

emik,
Why not [tt]Round()[/tt] or [tt]CInt()[/tt]?
[tt]Round(10847.84 / 1000) = 11
Cint(10847.84 / 1000) = 11

Round(10847.84 / 1000, 1) = 10.8
Cint(10847.84 / 100) / 10 = 10.8

Round(10847.84 / 100) = 108
Cint(10847.84 / 100) = 108[/tt]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[emik]

Hey I like the Round function. I know I sometimes have very basic questions, but it helps me learn.

Thanks for all your help, it's well appreciated!