435,404 Members | 1,937 Online
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
13 Replies

 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

 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 Public Function RoundUp(ToRound As Double) As String     Dim FixedQty As Long   Dim Temp As String   Dim TotQty As Double   TotQty = ToRound FixedQty = Fix(TotQty)     If FixedQty > 0 Then     Temp = CStr(FixedQty)   Else     Temp = CStr(0)   End If     Select Case TotQty - FixedQty     Case 0.01 To 0.09       Temp = Temp + 0.1     Case 0.11 To 0.19       Temp = Temp + 0.2     Case 0.21 To 0.29       Temp = Temp + 0.3     Case 0.31 To 0.39       Temp = Temp + .4     Case 0.41 To 0.49       Temp = Temp + 0.5     Case 0.51 To 0.59       Temp = Temp + 0.6     Case 0.61 To 0.69       Temp = Temp + 0.7     Case 0.71 To 0.79       Temp = Temp + 0.8     Case 0.81 To 0.89       Temp = Temp + 0.9     Case Is > 0.91       Temp = CStr(Int(TotQty) + 1)   End Select     RoundUp = Temp   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

 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 Public Function RoundUp(varInput As Variant, intDigits As Integer) As Variant     RoundUp = -Int(-varInput * 10 ^ intDigits) / 10 ^ intDigits End Function   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 Public Function RoundUp(ToRound As Double) As String     Dim FixedQty As Long   Dim Temp As String   Dim TotQty As Double   TotQty = ToRound FixedQty = Fix(TotQty)     If FixedQty > 0 Then     Temp = CStr(FixedQty)   Else     Temp = CStr(0)   End If     Select Case TotQty - FixedQty     Case 0.01 To 0.09       Temp = Temp + 0.1     Case 0.11 To 0.19       Temp = Temp + 0.2     Case 0.21 To 0.29       Temp = Temp + 0.3     Case 0.31 To 0.39       Temp = Temp + .4     Case 0.41 To 0.49       Temp = Temp + 0.5     Case 0.51 To 0.59       Temp = Temp + 0.6     Case 0.61 To 0.69       Temp = Temp + 0.7     Case 0.71 To 0.79       Temp = Temp + 0.8     Case 0.81 To 0.89       Temp = Temp + 0.9     Case Is > 0.91       Temp = CStr(Int(TotQty) + 1)   End Select     RoundUp = Temp   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 Public Function RoundUp(varInput As Variant, intDigits As Integer) As Variant     RoundUp = -Int(-varInput * 10 ^ intDigits) / 10 ^ intDigits End Function   Take a look also at this . Oct 8 '07 #6

 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

 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

 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

 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

 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