I would like to round UP to thousands, hundreds depending on value 1

[Senjen]

In a query I would like to round up to nearest thousand if over 500, to nearest hundred if over 50 and leave any smaller values alone. Any thoughts?

 

[SkipVought]

Hi,

IIF([MyField]>500,INT(([MyField]+1000)/1000)*1000,IIF([MyField]>50,INT(([MyField]+100)/100)*100,[MyField]))

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Senjen]

Skip your the best, just couldn't see it. Works perfectly.

 

[dgillz]

Hey Skip,

In Excel, you can use:

Round([your number],-3)

To get to the nearest thousand. Does this not work that way in Access?


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[SkipVought]

dg,

I suppose that it would, and it would be a simpler solution for sure. I just tend to think in terms of the basic math.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Senjen]

I tried the Round([your number],-3) and got an error before I posted. Looked good to me too. Could be the formatting of my data. Never really looked at it too hard.

 

[dgillz]

I also got the error. I guess it cannot be used in Access.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com