A simple Round up?? 1

[Ali29J]

Does anyone know of a simple round up function? all I need to do is round up the following calculation result to the nearest 10, all I can find is long and complicated coding??!?

[CostPlus10%] = [Cost] + ([Cost] * 0.1)

CostPlus10% being the field I require rounding up

Appreciate a simpler solution for a simpler mind!!

Ali

 

[hymn]

have a look at
thread701-864825

Hope this helps
Hymn

 

[Ali29J]

Hi I have tried every one of them, it wont budge, I have the following

cost = 70
Calculated result from above formula gives me:
CostPlus10% = 77, I need it to be 80! always to roundup to nearest 10.

I have tried format, round, all that was on thread, any other ideas?? just cant get it to work

 

[PHV]

Have you tried something like this ?
CostPlus10% =(((Cost * 1.1) + 9.999999) \ 10) * 10

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Craig0201]

A swift adaption of a function I wrote and published in FAQs in Other Topics ages ago.

Function RoundUp(Number As Double, Num_Digits As Integer) As Double

'Set up variables
Dim dblNumNum_Digits As Double
Dim intNumNum_Digits As Integer

'Move decimal point such that int will perform at correct level
dblNumNum_Digits = Number * (10 ^ Num_Digits)

'Integerise
intNumNum_Digits = Int(dblNumNum_Digits)

'Compare integer to see if needs rounding up
If dblNumNum_Digits - intNumNum_Digits > 0 Then
'round up
intNumNum_Digits = intNumNum_Digits + 1
Else
'round down
intNumNum_Digits = intNumNum_Digits
End If

'Return result, moving decimal point back
RoundUp = intNumNum_Digits / (10 ^ Num_Digits)

End Function

NumDigits to round up to the nearest 10 is -1.

Craig

 

[Ali29J]

phv, yours is closest yet! it rounded ok, but when faced with other values, such as 30000, it rounded to 33010, it seems to add 10 to all other values in the hundred and thousands

Craig, where do i put this coding? i tried to use under after update and does not function.

 

[RoyVidar]

Could something like this do (just of the top of my head, might need some tweaking)?

[tt]dim dblTmp as double
dblTmp=[Cost] + ([Cost] * 0.1)
if dblTmp mod 10 = 0 then
[CostPlus10%] = (dblTmp \ 10) * 10
else
[CostPlus10%] = (dblTmp \ 10 + 1) * 10
end if[/tt]

Roy-Vidar

 

[Craig0201]

Ali,

The code goes in a code module.

In the AfterUpdate event,

[CostPlus10%] = RoundUp(1.1*[Cost],-1)

Craig

 

[scottian]

Private Sub Command2_Click()
Dim a As Integer

If Right$(Me![Text0], 1) = 0 Then
End
Else
a = 10 - Right$(Me![Text0], 1)
Me![Text3] = Me![Text0] + a
End If

End Sub

"My God! It's full of stars...

 

[Ali29J]

royvidar, genius ! thankyou work a treat,

 

[hwkranger]

I know you already have your answer, but here's something from a long time ago that I wrote:

Public Const FORCEROUNDUP = 1
Public Const FORCEROUNDDOWN = 2
Public Const NOFORCEROUND = 3


Public Function fRounding(NumberBeingRounded As Double, Optional NumOfPlaces As Integer, Optional RoundUpOrDown As Integer = 3) As Double
'RoundUpOrDown - Pass FORCEROUNDUP, FORCEROUNDDOWN, NOFORCEROUND or pass nothing.
'Randall Vollen
'National City Bank
'This was created, because it was brought to my attention by someone that
'there is supposedly 'no way' to round in access. I looked into it further and found that
'there is no 'easy' way to do it. This is a very simplistic piece of code that takes in
'any number with any number of decimal places and rounds to the decimal of your choice.

'Mind that you may be led to believie the following will round for you:
'-1 * fix(number * -100)/100 or
'-1 * cint(number * -100)/100

'Unfortunately due to the nature of the truncation of Access's VBA if you use an EVEN Number that happens to fall on
'a half (82.5) then it will always round down- otherwise that is a great one line train of thought.

'*** stuff to delete ***
'TEST DATA that was thrown in:
'Dim NumberBeingRounded As Double
'NumberBeingRounded = 83.55769921
'Dim NumOfPlaces As Integer
'NumOfPlaces = 7
'*** end of stuff to delete ***

Dim y As Long 'this holds whole part of the number or the part that will eventually be rounded
Dim z As Double 'this is the part that decideds if the number is rounded up or down
Dim Factor As Double 'this holds number 1 - 1,000,000 that decides to what place to round
Dim HalfPoint As Double

If RoundUpOrDown = NOFORCEROUND Then
'No Forcing at all
HalfPoint = 0.5
ElseIf RoundUpOrDown = FORCEROUNDUP Then
'Force rounding up
HalfPoint = 0
Else
'Force rounding down
HalfPoint = 1
End If

'if you don't specify the number of places, then it's rounded to the nearest whole number
If IsNull(NumOfPlaces) Then
NumOfPlaces = 0
End If

'If NumOfPlaces > 7 Then

'sets which decimal to round to
Factor = 10 ^ NumOfPlaces

'multiply number by factor, so we can round
NumberBeingRounded = NumberBeingRounded * Factor

'rounding
y = Fix(NumberBeingRounded)
z = NumberBeingRounded - y
NumberBeingRounded = Fix(NumberBeingRounded)
If z >= HalfPoint Then
NumberBeingRounded = NumberBeingRounded + 1
End If
'end of rounding

'return number back to it's original
NumberBeingRounded = NumberBeingRounded / Abs(Factor)

'return value
fRounding = NumberBeingRounded
End Function





Randall Vollen
National City Bank Corp.