Rounding to the nearest million

[robertlevine]

I'm working with queries in Access97, and would like my query to round a number to the nearest million.

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!

 

[FoFa]

How about something like:
ROUND(MyNum/1000000)

 

[KKilfoil]

Close...

How about something like:
ROUND(MyNum/1000000)*1000000

 

[robertlevine]

maybe i'm not referencing the correct dll's, but I don't have a ROUND function in Access97

 

[Mile-O]

How about:

Int((MyNum/100000)+0.5)*100000

 

[KKilfoil]

watch those zero's!

Int((MyNum/1000000)+0.5)*1000000

 

[robertlevine]

Thanks for the help, mile-o-phile... changed it to 1000000 for millions and it worked great so far! just have to do a little testing to make sure!

you guys are great at thinking outside the box!

 

[Mile-O]

Very true. Very, very true!

 

[KKilfoil]

Actually, that's been the accepted way to roundoff since the Fortran and COBOL days.

 

[Mile-O]

More true.

I don't, however, remember those days.

 

[robertlevine]

well... it's new to me! thanks kkil.