CODEWITHSUNDEEP
vbams-access
Rats
Rats

Reputation: 39

Is there a way to make sure the round function always rounds up?

Question is 2 parts. 1st - I have a simple rounding function for a column in my query but I need it to round up in all cases. 2nd - this calculation yields multiple records for each calculation

NumOfBoxes: Round([Orders].[Qty]/[PartsFinished].[BoxQuantity],0)

With regard to the multiple records it does this - Example: order qty is 1000, box qty is 250, NumOfBoxes is 4 but this returns 4 separate lines instead of just one. If the answer to the calculation is 15 it returns 15 lines. I have no idea why.

Upvotes: 1

Views: 536

Answers (3)

Gustav
Gustav

Reputation: 55906

Use the core method for rounding up:

NumOfBoxes: -Int(-[Orders].[Qty]/[PartsFinished].[BoxQuantity])

You may also wish study my article at Experts Exchange and code collection on GitHub:

Rounding values up, down, by 4/5, or to significant figures

VBA.Round

If you have no EE account, browse for the link: Read the full article

Upvotes: 1

Zelig
Zelig

Reputation: 1808

To systematically round up a number, simply use round(number+0,5). You can also use ceil(number).

Upvotes: 0

user2191247
user2191247

Reputation:

This should work:

Int(([Orders].[Qty] + [PartsFinished].[BoxQuantity] - 1)/[PartsFinished].[BoxQuantity])

Int truncates, but you're in effect adding a number less than one, so this will always round up, unless the number is exactly an integer, in which case it won't round up. This is probably the behavior you're looking for.

Upvotes: 0

Related Questions