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

Ms Access Round Up

P: 5
I need to create a Roundup function in Access for example.....

36.11 to 36.2
44.43 to 44.5
55.55 to 55.6
32.51 to 32.6
21.67 to 21.7

PS: I'm an amateur at writing VBA code and creating modules within Access
Oct 8 '07 #1
Share this Question
Share on Google+
13 Replies


Scott Price
Expert 100+
P: 1,384
Hi Te,

Welcome to the Scripts!

You have inadvertently posted your question in our Articles section. I have moved it across for you to our main forum where it is much more likely to get a response.

MODERATOR
Oct 8 '07 #2

Scott Price
Expert 100+
P: 1,384
The normal function that VBA/Access provides is called Round(), however it will not do what you are asking, since it rounds to the nearest decimal place, not the next highest decimal place.

This being the case, you'll have to write a Select Case function to do what you are asking.

This should do it:

Expand|Select|Wrap|Line Numbers
  1. Public Function RoundUp(ToRound As Double) As String
  2.  
  3.   Dim FixedQty As Long
  4.   Dim Temp As String
  5.   Dim TotQty As Double
  6.  
  7. TotQty = ToRound
  8. FixedQty = Fix(TotQty)
  9.  
  10.   If FixedQty > 0 Then
  11.     Temp = CStr(FixedQty)
  12.   Else
  13.     Temp = CStr(0)
  14.   End If
  15.  
  16.   Select Case TotQty - FixedQty
  17.     Case 0.01 To 0.09
  18.       Temp = Temp + 0.1
  19.     Case 0.11 To 0.19
  20.       Temp = Temp + 0.2
  21.     Case 0.21 To 0.29
  22.       Temp = Temp + 0.3
  23.     Case 0.31 To 0.39
  24.       Temp = Temp + .4
  25.     Case 0.41 To 0.49
  26.       Temp = Temp + 0.5
  27.     Case 0.51 To 0.59
  28.       Temp = Temp + 0.6
  29.     Case 0.61 To 0.69
  30.       Temp = Temp + 0.7
  31.     Case 0.71 To 0.79
  32.       Temp = Temp + 0.8
  33.     Case 0.81 To 0.89
  34.       Temp = Temp + 0.9
  35.     Case Is > 0.91
  36.       Temp = CStr(Int(TotQty) + 1)
  37.   End Select
  38.  
  39.   RoundUp = Temp
  40.  
  41. End Function
This code will go into a standard code module (In VBA editor window, click Insert>Module). You can then call it from a query, a control on a form, etc. Just pass it the number to round and it will return the result.

Caveats:

This assumes that you will be working with two decimal places and no more... If you have a number of 1.001 this function won't fire to round it to 1.1 (nor should there be any need to that I can see...)

This will return the number formatted as a string. If you need it formatted as a number we'll have to change the declarations.

Regards,
Scott
Oct 8 '07 #3

FishVal
Expert 2.5K+
P: 2,653
I need to create a Roundup function in Access for example.....

36.11 to 36.2
44.43 to 44.5
55.55 to 55.6
32.51 to 32.6
21.67 to 21.7

PS: I'm an amateur at writing VBA code and creating modules within Access
Hi, Kuini.

Expand|Select|Wrap|Line Numbers
  1. Public Function RoundUp(varInput As Variant, intDigits As Integer) As Variant
  2.     RoundUp = -Int(-varInput * 10 ^ intDigits) / 10 ^ intDigits
  3. End Function
  4.  
Take a look also at this .
Oct 8 '07 #4

P: 5
Great, thanks Scott. I''ve inserted the code into a new module however, I've tried to call the function from a query (Kgs/Sheet: RoundUp([Th Kgs]) but I receive the error message "Undefined function 'Roundup' in expression".

Also, I need the result to be formatted as a number not as a string.

Many thanks, Te Kuini

The normal function that VBA/Access provides is called Round(), however it will not do what you are asking, since it rounds to the nearest decimal place, not the next highest decimal place.

This being the case, you'll have to write a Select Case function to do what you are asking.

This should do it:

Expand|Select|Wrap|Line Numbers
  1. Public Function RoundUp(ToRound As Double) As String
  2.  
  3.   Dim FixedQty As Long
  4.   Dim Temp As String
  5.   Dim TotQty As Double
  6.  
  7. TotQty = ToRound
  8. FixedQty = Fix(TotQty)
  9.  
  10.   If FixedQty > 0 Then
  11.     Temp = CStr(FixedQty)
  12.   Else
  13.     Temp = CStr(0)
  14.   End If
  15.  
  16.   Select Case TotQty - FixedQty
  17.     Case 0.01 To 0.09
  18.       Temp = Temp + 0.1
  19.     Case 0.11 To 0.19
  20.       Temp = Temp + 0.2
  21.     Case 0.21 To 0.29
  22.       Temp = Temp + 0.3
  23.     Case 0.31 To 0.39
  24.       Temp = Temp + .4
  25.     Case 0.41 To 0.49
  26.       Temp = Temp + 0.5
  27.     Case 0.51 To 0.59
  28.       Temp = Temp + 0.6
  29.     Case 0.61 To 0.69
  30.       Temp = Temp + 0.7
  31.     Case 0.71 To 0.79
  32.       Temp = Temp + 0.8
  33.     Case 0.81 To 0.89
  34.       Temp = Temp + 0.9
  35.     Case Is > 0.91
  36.       Temp = CStr(Int(TotQty) + 1)
  37.   End Select
  38.  
  39.   RoundUp = Temp
  40.  
  41. End Function
This code will go into a standard code module (In VBA editor window, click Insert>Module). You can then call it from a query, a control on a form, etc. Just pass it the number to round and it will return the result.

Caveats:

This assumes that you will be working with two decimal places and no more... If you have a number of 1.001 this function won't fire to round it to 1.1 (nor should there be any need to that I can see...)

This will return the number formatted as a string. If you need it formatted as a number we'll have to change the declarations.

Regards,
Scott
Oct 8 '07 #5

P: 5
Thanks FishVal. Will give it a go.

Hi, Kuini.

Expand|Select|Wrap|Line Numbers
  1. Public Function RoundUp(varInput As Variant, intDigits As Integer) As Variant
  2.     RoundUp = -Int(-varInput * 10 ^ intDigits) / 10 ^ intDigits
  3. End Function
  4.  
Take a look also at this .
Oct 8 '07 #6

missinglinq
Expert 2.5K+
P: 3,532
I''ve inserted the code into a new module
You didn't name the module the same name as the function, did you? This is a common mistake,and it confuses the Access gnomes! If the function and the module have the same name, change the module's name.

Welcome to TheScripts!

Linq ;0)>
Oct 9 '07 #7

P: 5
Ooops thanks Linq...I've changed the name of the module and it works just fine


You didn't name the module the same name as the function, did you? This is a common mistake,and it confuses the Access gnomes! If the function and the module have the same name, change the module's name.

Welcome to TheScripts!

Linq ;0)>
Oct 9 '07 #8

P: 5
Hi Scott - sometimes I get the wrong result from the round up function e.g.:

36.10 returns 36
72.90 " 72
36.11 " 36
32.51 " 32

Can you help????

Great, thanks Scott. I''ve inserted the code into a new module however, I've tried to call the function from a query (Kgs/Sheet: RoundUp([Th Kgs]) but I receive the error message "Undefined function 'Roundup' in expression".

Also, I need the result to be formatted as a number not as a string.

Many thanks, Te Kuini
Oct 9 '07 #9

P: 1
I have to say, after spending an entire afternoon of looking everywhere to get some help with what seemed like a simple problem, you finally helped me.

Thank you, thank you, thank you!

Leslie
Oct 11 '07 #10

Scott Price
Expert 100+
P: 1,384
Hi Scott - sometimes I get the wrong result from the round up function e.g.:

36.10 returns 36
72.90 " 72
36.11 " 36
32.51 " 32

Can you help????
Hi Te Kuini,

This likely is resulting because of the String data type. Try changing from String to Double (in the first line of the function code).

Change the declaration for the Temp variable from String to Double as well.

Regards,
Scott
Oct 12 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
Thanks FishVal. Will give it a go.
I must register in this thread.
I saw it the other day then, coincidentally, needed to reproduce it for my own purposes. I hunted everywhere for it (assuming it was a registered thread of course) but couldn't find it. Luckily enough I had remembered the gist of what FishVal had posted (Post #4) and, as it happens, it seems to match exactly what I remembered. Of course it works (very elegant code I would say) and now I have a version of it in my database module :)

BTW I'm curious how you got on with it?
No disrespect to you, Scott, or your code, but the one-liner seems to do the whole job without complication.
Oct 12 '07 #12

Scott Price
Expert 100+
P: 1,384
Actually I'm impressed with Fish's code also :-)

If the OP wishes to go with that solution (which is more elegant, yes) than I'd be quite happy with that also!

That said, in defense of the Select Case method: It's more flexible for expanding to different uses, for example, assigning fractional signs to the decimal values (which is actually what I adapted this code from to begin with). However, if the need is confined solely to the rounding, then Fish's code is a simpler solution.

Regards,
Scott
Oct 12 '07 #13

NeoPa
Expert Mod 15k+
P: 31,494
I agree Scott. I'm a big fan of the Select Case construct myself, and the scenario you describe would be a good use for it.
Oct 12 '07 #14

Post your reply

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