5

On a report I have the following code for a field:

=Sum([PartQty]*[ModuleQty])

Example results are 2.1 and 2.6. What I need is for these value to round up to the value of 3. How can I change my field code to always round up the results of my current expression?

RLH
  • 15,230
  • 22
  • 98
  • 182
Shaun
  • 99
  • 2
  • 2
  • 8
  • Sorry Sam. Not been using this site long, how do you vot and accept answers, you are the first peron to mention this. You have been very helpful. Shaun – Shaun Apr 01 '10 at 13:35
  • 1
    no problem :). to vote click on the arrows above or below the number in the top left of the answers. click the up arrow to vote that the answer is useful, the down arrow if it is not. leave a comment if you downvote to say why. Click the tick symbol next to the answer that you accept as being the answer to your question. it'll go green to show that it is the accepted solution. – Sam Holder Apr 01 '10 at 13:42

3 Answers3

7

This is an old Access trick I learned a very long time ago, and it makes use of the way Access handles fractional, negative numbers. Try this:

-Int(-[DecimalValue])

It's odd, but it will always round your numbers up to the nearest whole number.

RLH
  • 15,230
  • 22
  • 98
  • 182
  • Yes, I agree. I'm not sure if this is a bug but it's worked for years and, in fact, I used it just the other day in Access 2010. So, it's still useful. Also, is quite terse and as long as you understand the purpose of the syntax, it is the easiest way to express that you want to "round up" a number. – RLH Dec 20 '12 at 19:54
  • I am fairly sure it is not a bug, it is rounding down, isn't it, in each case? `-1.4=-2`, `1.4=1` – Fionnuala Dec 20 '12 at 20:01
  • Yes, but the odd thing is why does it always round down negative numbers? i.e. -1.4=-2 and -1.9 = -2. Should rounding any other, positive integer behave the same way when rounding them? – RLH Dec 20 '12 at 20:04
  • 1 is 1.1 to 1.9 rounded down, -1.1 to -1.9 rounded down is -2, yesno? -1 is higher than -1.1, so -1 would be rounding up. – Fionnuala Dec 20 '12 at 20:09
  • @Remou: Yes, you are correct. The reason why this is peculiar is negative numbers behave differently than positive numbers. Any number between -x.1 and -x.9 always rounds to -x. However, positive numbers follow "Banker's rounding" rules. (see: http://en.wikipedia.org/wiki/Banker%27s_rounding#Round_half_to_even) – RLH Dec 20 '12 at 20:33
  • 1
    Sorry, but my point is that negative numbers behave **just the same** as positive numbers, they round down with Int. – Fionnuala Dec 20 '12 at 20:36
  • 1
    With Round(-1.1,0)=-1, Round(-1.9,0)=-2 Round is not the same as Int. – Fionnuala Dec 20 '12 at 20:38
  • Ah, you are correct. I was forgetting, what I am reference is when you pass the value through the ROUND() function. ROUND uses Bankers rules, Int() always rounds down. Apologies. – RLH Dec 20 '12 at 20:40
2

Test this: Round(yournumber + 0.5, 0)

isxaker
  • 8,446
  • 12
  • 60
  • 87
1

you could do 

=Int(Sum([PartQty]*[ModuleQty]))+1

I think. That would get the Int part of the sum (2) and then add 1. you might need to be a little more clever as this will probably give you 3 even if the sum is exactly 2, which is probably not what you want.

not tested it but something along these lines might work (access syntax is not that great, but should give you the right idea) :

Iif(Sum([PartQty]*[ModuleQty])-Int(Sum([PartQty]*[ModuleQty]))=0,
     Sum([PartQty]*[ModuleQty]),
     Int(Sum([PartQty]*[ModuleQty]))+1)
Sam Holder
  • 32,535
  • 13
  • 101
  • 181
  • Hi Sam. You are right about your first idea, if the value is 2 it will round up to 3. Will try the next idea soon. Cheers – Shaun Apr 01 '10 at 10:51
  • Hi Sam. I have tried the new code, I keep getting an error message regarding the comma's. Shaun. – Shaun Apr 01 '10 at 12:58
  • Hi Sam I have replaced the first comma with Then and the second comma with = still not working getting errors – Shaun Apr 01 '10 at 13:00
  • 1
    http://www.techonthenet.com/access/functions/advanced/iif.php might help with the syntax... this: http://www.databasedev.co.uk/count_values_countiif.html implies that you can use it in a report. try breaking it down to see if you can get the syntax correct: IIf(2-1=0,2,1+1) should be correct syntax from what I can see. and make sure you are using IIF and not IF – Sam Holder Apr 01 '10 at 13:34