roundUp Function

[rdw456]

Hell All
I am looking for a function to roundup currency. Exel has this function but Access does not.

Rounds a number up, away from 0 (zero).
Syntax
ROUNDUP(number,num_digits)

Number is any real number that you want rounded up.
Num_digits is the number of digits to which you want to round number.

Remarks
• ROUNDUP behaves like ROUND, except that it always rounds a number up.
• If num_digits is greater than 0 (zero), then number is rounded up to the specified number of decimal places.
• If num_digits is 0, then number is rounded up to the nearest integer.
• If num_digits is less than 0, then number is rounded up to the left of the decimal point.

Example

Description (Result)
=ROUNDUP(3.2,0) Rounds 3.2 up to zero decimal places (4)
=ROUNDUP(76.9,0) Rounds 76.9 up to zero decimal places (77)
=ROUNDUP(3.14159, 3) Rounds 3.14159 up to three decimal places (3.142)
=ROUNDUP(-3.14159, 1) Rounds -3.14159 up to one decimal place (-3.2)
=ROUNDUP(31415.92654, -2) Rounds 31415.92654 up to 2 decimal places to the left of the decimal (31500)

My purpose is we have two cash registers that roundup and my program tax never comes close over a weekly period which is what we use in our reports that go to the accountant. I am trying to get close.

I have tried several idea,s from the net witch did not work that could be me I am pretty novice.

Thanks bob

 

[CJ_London]

what have yo tried so far - please provide some examples - usually easier to try and fix something than spend time reinventing the wheel

 

[NauticalGent]

I refer you to the legend, Mr. Allen Browne:

http://allenbrowne.com/ser-44.html
or
http://allenbrowne.com/round.html

He has since retired but his legacy lives on...

 

[rdw456]

Thanks CJ_London for your response. I had to go back to check out my requirements and did a query to Exel and checked out the roundup function there and it did not produce the results I expected.

I have code that takes $4.75 tax in and calculates the base price.

Code
---------------------------------------------------------------------------------------------
Private Sub txtpriceTaxIn_AfterUpdate()

On Error GoTo HandleError

Me.txtNewPrice = Round(Me.txtpriceTaxIn / (1 + Me.TaxRate), 4)

Exit Sub

HandleError:
If Error <> 0 Then
Resume Next
Else
GeneralErrorHandler Err.Number, Err.Description, "modBusinessLogic", _
"txtpriceTaxIn_AfterUpdate"
Resume Next
End If

Exit Sub

End Sub
---------------------------------------------------------------------------------------------
want to round this down to cents as Access stores this value to four decimal places. I have the till tape from the cash register which gives me the amount that tax is calculated on that amount. This is done over a week and the difference is quiet a bit so trying to get closer or spot on.

Thanks Bob

 

[CJ_London]

ok, you want something that is specific to currency and will always be positive. Further this is based on a calculation to work backwards from a tax inclusive gross figure to a net figure.

Since it involves tax, your tax authority will have guidance as to how tax should be calculated and the treatment of rounding - normally it is simply rounding on a line by line basis without any bias to up or down.

The original examples you gave would appear to not be relevant - please can you provide some more - gross figure, calculated net figure and required net figure.

but you may find this is sufficient

Round((Me.txtpriceTaxIn / (1 + Me.TaxRate))+0.00005, 4)

 

[rdw456]

Hi All

I went back to the drawing board to check what results I needed and found that the cash register dealer who told me that that the cash rounds up was not correct. I used a query to load the data into Exel and the tried all the different rounding options and found that rounding down is what I need. I used the following code

Code
---------------------------------------------------------------------------------------------
Private Sub txtpriceTaxIn_AfterUpdate()

Dim Price As Currency

On Error GoTo HandleError

Price = Me.txtpriceTaxIn / (1 + Me.TaxRate)

Me.txtNewPrice = Int(100 * Price) / 100

Exit Sub

HandleError:
If Error <> 0 Then
Resume Next
Else
GeneralErrorHandler Err.Number, Err.Description, "modBusinessLogic", _
"txtpriceTaxIn_AfterUpdate"
Resume Next
End If

Exit Sub

End Sub
---------------------------------------------------------------------------------------------

This works and puts 00 on the last two decimal places of the currency. I use this number to update the sales price table So Far so good
I now want to go to the transaction table and update all the transactions to the new value via a update query. But I get errors for data mismatch so any ideas would be welcome.

Thanks Bob

 

[CJ_London]

So as suggested by NG.

With regards your error, best guess is there is something wrong with your update query. Can't say any more without knowing what it is and how you run it