Rounding Up

[RedHeadedMonster via AccessMonster.com]

Im trying to RoundUp Currency in a QUERY for a Bill of Materials. I've
searched thru previoius posts for rounding and none seemed to work for me.
So heres the situation:

If I have a Part that is .68 [PartCost] for a package of 50 [QtyPerBox]. If
the customer only needs 1 part, the price would be .68 / 50 which equals .
0014. Round function then sets the value at 0.00, I need a round function
that will round up to the nearest whole penny. So in this case it would
round to 0.01.

Another example. The part is $14.04 for a package of 50. so $14.04 / 50 = .
2808. Round function turns this to .28 and I need it to round to .29.

currently using the following formula: Round([PartCost]/[QtyPerBox],2)

Any help is GREATLY appreciated. Thanx!
RHM

 

[RedHeadedMonster via AccessMonster.com]

Sorry, I forgot to let you know that [PartCost] is set to Currency and
[QtyPerBox] is set to Number - Long Integer.

 

[John Spencer]

Try the following. This will calculate a price per part in whole cents.

-Int(-(PartCost/QtyPerBox)*100)/100

If the person wants 10 items (out of quantity of 20) do you want to charge at
10 times the single part number or just for half a box. The two could be
significantly different.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[RedHeadedMonster via AccessMonster.com]

I just tried your formula, worked like a charm.

No idea on the half a box scenario. When they bill they bill at the per part
rate. Im guessing its a profit margin thing.

Thanks for the formula!

RHM

Try the following. This will calculate a price per part in whole cents.

-Int(-(PartCost/QtyPerBox)*100)/100

If the person wants 10 items (out of quantity of 20) do you want to charge at
10 times the single part number or just for half a box. The two could be
significantly different.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Sorry, I forgot to let you know that [PartCost] is set to Currency and
[QtyPerBox] is set to Number - Long Integer.

 

[RedHeadedMonster via AccessMonster.com]

Well I thought it was working. Its doing something strange now. I found
this problem twice now.

Heres what its doing. I have a part that is listed at $19.69, 1 per package.
Yet the query shows it as $19.70 each. The other one is listed at $137.27,
1 per package. Yet the query shows it as 137.28.

So why is it changing the hard coded price as more than what has been hard
entered into the database? I just checked the first three pages of 17 pages.
There are other parts that the set price at 1 per package and the query is
pulling the right price.

Any idea how or why this is occurring?

RHM

Try the following. This will calculate a price per part in whole cents.

-Int(-(PartCost/QtyPerBox)*100)/100

If the person wants 10 items (out of quantity of 20) do you want to charge at
10 times the single part number or just for half a box. The two could be
significantly different.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Sorry, I forgot to let you know that [PartCost] is set to Currency and
[QtyPerBox] is set to Number - Long Integer.

 

[Michel Walsh]

Your database data type is double precision, or single precision, not
currency (or decimal):

? -Int(-(137.27/1)*100)/100
137.28

? -Int(-(CCur(137.27)/1)*100)/100
137.27



The result is right if the argument is Currency, or Decimal, but wrong for
approximate data type (double or single precision floating point).



Vanderghast, Access MVP

Well I thought it was working. Its doing something strange now. I found
this problem twice now.

Heres what its doing. I have a part that is listed at $19.69, 1 per
package.
Yet the query shows it as $19.70 each. The other one is listed at
$137.27,
1 per package. Yet the query shows it as 137.28.

So why is it changing the hard coded price as more than what has been hard
entered into the database? I just checked the first three pages of 17
pages.
There are other parts that the set price at 1 per package and the query is
pulling the right price.

Any idea how or why this is occurring?

RHM

Try the following. This will calculate a price per part in whole cents.

-Int(-(PartCost/QtyPerBox)*100)/100

If the person wants 10 items (out of quantity of 20) do you want to charge
at
10 times the single part number or just for half a box. The two could be
significantly different.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Sorry, I forgot to let you know that [PartCost] is set to Currency and
[QtyPerBox] is set to Number - Long Integer.

 

[RedHeadedMonster via AccessMonster.com]

[QtyPerBox] was set to Long Integer. I changed it to decimal and Im back in
business.

Thanks a million for your help!

RHM

Your database data type is double precision, or single precision, not
currency (or decimal):

? -Int(-(137.27/1)*100)/100
137.28

? -Int(-(CCur(137.27)/1)*100)/100
137.27

The result is right if the argument is Currency, or Decimal, but wrong for
approximate data type (double or single precision floating point).

Vanderghast, Access MVP

Well I thought it was working. Its doing something strange now. I found
this problem twice now.

[quoted text clipped - 31 lines]

Sorry, I forgot to let you know that [PartCost] is set to Currency and
[QtyPerBox] is set to Number - Long Integer.