By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Rounding Up in Access

100+
P: 418
I have a field called GrossAmount that shows amount in two decimal format. I need to calculate a field called NetAmount where the amount needs to be rounded up. How do I do it in Access? Anyone out there to help, please?

Data type for both the fields are "Currency"

Thanks.
Apr 27 '09 #1
Share this Question
Share on Google+
16 Replies


Expert 100+
P: 1,287
Since the Round() function uses round-to-even logic, I would be more likely to use:
Expand|Select|Wrap|Line Numbers
  1. IIf( amount = Int( amount ), amount, Int( amount ) + 1)
If the amount is an integer, return the amount, otherwise return the integer part of the amount plus 1.
Apr 27 '09 #2

FishVal
Expert 2.5K+
P: 2,653
Rounding .
Apr 27 '09 #3

100+
P: 418
Thanks. I will try and shall get back.
Apr 27 '09 #4

Expert 100+
P: 1,287
Thanks Fish, that's pretty interesting.
Expand|Select|Wrap|Line Numbers
  1. -Int( -amount )
Much better.
Apr 27 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
Certainly the best approach to the problem (that I've ever discovered).

The beauty is that the Int() function is not restricted to returning only an Integer. It's return is a Variant. It can handle even more digits than a Long. This should not be confused with the CInt() function.

Remember for currency though, that you need to multiply by 100, then divide the result, to get it to round at the penny or cent position. Be careful to ensure the calculation is not treated as a Long or Integer. If it is, the division will fail to work as expected.
Apr 27 '09 #6

Expert 100+
P: 1,287
I just thought of
Expand|Select|Wrap|Line Numbers
  1. Round(amount+.499)
which should work since the amount should only go to 2 decimal places. Probably Int() is faster though.

NeoPa: I don't understand what you mean by multiply by 100, then divide the result, when rounding a currency or decimal to an integer.
Apr 28 '09 #7

100+
P: 418
ChipR:

Is this the correct way to write the formula in the control for NetAmount?

= int([GrossAmount])

Thanks.
Apr 28 '09 #8

Expert 100+
P: 1,287
If you want it to be rounded up, you would need to use
Expand|Select|Wrap|Line Numbers
  1. = -Int(-[GrossAmount])
Since Int() rounds numbers down. When we make the number negative and round down, we get a bigger negative number, then we just negate it again and get the positive.
Like:
Expand|Select|Wrap|Line Numbers
  1. -Int(-[5.5]) = -Int(-5.5) = -(-6) = 6
Apr 28 '09 #9

100+
P: 418
ChipR:

It works but not the way I need it to. Perhaps I didn't explain my problem clearly. Let's say if the GrossAmount is $100.39, I need the NetAmount to show as $100. If the GrossAmount is $100.67, then the NetAmount is to read as $101.

With the formula = -Int(-[GrossAmount]), $100.39 becomes $101 as NetAmount and the GrossAmount is displayed as $100. Question: Why does the GrossAmount gets changed? It should display as what is entered, in this case it was $100.39.

Thanks.
Apr 28 '09 #10

Expert 100+
P: 1,287
I was under the impression that you wanted to round up, since that is what you said in your first post. To do normal rounding, do:
Expand|Select|Wrap|Line Numbers
  1. =Round([GrossAmount]+.0001)
Apr 28 '09 #11

100+
P: 418
ChipR: It's working great. Many Thanks.
Apr 28 '09 #12

Expert 100+
P: 1,287
No problem, interesting topic.
Apr 28 '09 #13

FishVal
Expert 2.5K+
P: 2,653
Gentlemen, here is a thread where some interesting aspects of Round() function were discussed.
Apr 28 '09 #14

NeoPa
Expert Mod 15k+
P: 31,709
@ChipR
This depends on the requirement.

If the requirement is to round the pence up (to a whole currency amount - , $ etc), then this wouldn't be required.

If, on the other hand, it is to round up a calculated result to the next higher penny or cent value, then you would need something like :
Expand|Select|Wrap|Line Numbers
  1. =CCur(-Int(-[Calculated Currency Value]*100) / 100
Apr 28 '09 #15

Expert 100+
P: 1,287
Ah, I see what you mean. Thanks for clarifying.
Apr 28 '09 #16

NeoPa
Expert Mod 15k+
P: 31,709
Always happy to help Chip. Especially those who contribute so much to the site :)
Apr 28 '09 #17

Post your reply

Sign in to post your reply or Sign up for a free account.