Ms Access Round Up

[Te Kuini]

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

[Scott Price]

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

[Scott Price]

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:

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

[FishVal]

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.

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 .

[Te Kuini]

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:

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

[Te Kuini]

Thanks FishVal. Will give it a go.

Hi, Kuini.

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 .

[missinglinq]

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)>

[Te Kuini]

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)>

[Te Kuini]

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

[DallasLDY]

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

[Scott Price]

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

[NeoPa]

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.

[Scott Price]

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

[NeoPa]

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.