CAN ACCESS ROUNDUP?

[need help with rounding in access]

In need to roundup a simple markup formula for pricing. Does Access permit
the rounding up to the nearest $10?

 

[Douglas J. Steele]

Yes, but you have to write the function yourself.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"need help with rounding in access" <need help with rounding in
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Jerry Porter]

I think the following will work:

Int(MyAmount/10 + .99) * 10

Replace "MyAmount" by your field.

It divides by 10 so the rounding will happen in the 10's place. Then
multiplies by 10 to restore the amount. The Int functions always rounds
down, but since .99 was added first, it rounds down to the higher
number.

Jerry

 

[jwr]

I have a similar problem.

I have created a report from a query. In the query, I have the following
format:

Essett Payment: [PaymentAmount]*0.95

When I create the report, I want a total of the Essett Payment. If, for
instance, I have 8 plus 9, the report puts 16 instead of 17 for the total.
On the report, there is a Payment Amount and essett payment. The formula I
have for the total of Essett payments is:

=Sum(Round([Essett Payment],2))

If I just enter = Sum ([Essett Payment]), it is still one penny short. I
have also tried =Round(Sum([Essett Payment]),2) and it does not work.

Any suggestions?
JR

 

[Jerry Porter]

I don't know if you're talking dollars or cents. I'm thinking it should
be cents, since you're rounding to the nearest penny. So your total
Essett Payment is (.08 + .09) * .95 = .1615
And you want that to round up to .17. Is that right?

Try:
Round(Sum([Essett Payment] + .005 ),2)

 

[James A. Fortune]

In need to roundup a simple markup formula for pricing. Does Access permit
the rounding up to the nearest $10?

In:

http://groups.google.com/group/microsoft.public.access/msg/d1b5b764da495d5e

Van T. Dinh
MVP (Access)

states:

You can use:

- Int( - [YourNumber] )


e.g.:


?-Int(-3.2)
4

That was a very clever idea. Graphing Int(x), flipping everything
across the Y axis (Right/Left) then flipping everything across the X
axis (Up/Down) shows that that ceiling function is good for all values of X.

Now, adjust the function so that it rounds up to the nearest 10:

-10 * Int(-X / 10#)

The '/ 10#' scales X up by a factor of 10 since 10 units in the new
coordinate system corresponds to 1 unit in the old coordinate system.
The '10 *' scales Y up by a factor of 10 since on the other side of the
equation it looked like Y/10.

Sample calculations:

-10 * Int(-32 / 10#) = -10 * 4 = 40 (32 -> 40)
-10 * Int(--5 / 10#) = -10 * 0 = 0 (-5 -> 0)
-10 * Int(--12 / 10#) = -10 * 1 = -10 (-12 -> -10)
-10 * Int(CCur(12) / 10#) = -10 * 1 = -1 (-$12 -> -10)

Perhaps throw in an NZ() around the field name to guard against Null
field values.

James A. Fortune
(e-mail address removed)

 

[jwr]

That worked ! Thank you.