Round a field to the nearest multiple of another field

[trulyblessed]

Hi. I have a qty field and a pack size field. I need to be able to round the qty field to the nearest multiple of the pack size field. How do I go about that?

Any help would be appreciated!
Thanks,
Kim

 

[Golom]

Try this

SELECT Qty, Pack, 

       iif ((qty mod pack) <= pack/2, 
            int(qty/pack) * pack, 
            qty + (pack - (qty mod pack))) As [Rounded]

FROM tbl

 

[trulyblessed]

Thank you so much. This worked beautifully. Sorry it took me so long to post a thank you, but your help is really appreciated.

Kim