rounding (ceiling?) function on a display subform

[ddt]

I have a form for searching among the records of a
database. It's based on a query, and the result is shown
on a read-only pop-up subform. That's all fine and works
well.

My problem is that on this subform I should round up/down
the numbers to the next 200. For example, 23432 should
display as 23400, and 23511 as 23600. In excel it is easy
with the built-in function CEILING, but I'm unable to
find a similar solution for this in Access. I think I
should use some predefined function of a modul, but to be
honest I got totally confused even about the way modules
should be used...

I'm a "must-be" Access user, started to learn the whole
thing for this single database, so please be as detailed
as you can - just like when talking to a complete idiot

 

[Michel Walsh]

Hi,

If x is to be rounded, then (VBA 6) :


RoundedNumber = Quantum * Int( 0.5+ x / CDec( Quantum ) )


in you case Quantum = 200



Hoping it may help,
Vanderghast, Access MVP

 

[JohnWL]

I like that, Michael. I've always had a sense that there was a simple
mathematical formula for this sort of thing, and this finally says it
clearly and simply. I'm adding this to my toolbox. I tried it with ddt's
request as
txtNumberRounded = 200 * Int(0.5 + txtNumberToRound / CDec(200))
and it worked perflectly.

 

[Guest]

Hi,
thanks for the answer.

It is surely me, but I can't make it to work. I always
got the error message "Expression contains unvalid number
of arguments". What do I do wrong?

 

[Michel Walsh]

Hi,


Maybe due to CDec. If you have VBA6, you have CDec, else, with Access 97,
removed it...


The problem is that if you type 0.1, then, you don't have exactly 0.1,
but maybe 0.09999996.... Using CDec(0.1), you have exactly 0.1, no
compromised done, but CDec is only available since Access 2000. Note that if
your quantum is an integer or a sum of power of 2, like 0.5, 0.25, 0.75,
0.125, etc., there is no problem either.

So,

200 * Int( 0.5 + myNumber / 200 )


should be fine on any system.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

God, it works perfectly! (don't ask how I did it, I could
never tell

Thanks both of you!!

ddt