 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008Rounding Up
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 Sorry, I forgot to let you know that [PartCost] is set to Currency and [QtyPerBox] is set to Number - Long Integer. 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. 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. > [quoted text clipped - 11 lines] >> Sorry, I forgot to let you know that [PartCost] is set to Currency and >> [QtyPerBox] is set to Number - Long Integer. 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. > [quoted text clipped - 11 lines] >> Sorry, I forgot to let you know that [PartCost] is set to Currency and >> [QtyPerBox] is set to Number - Long Integer. 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. [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): [quoted text clipped - 15 lines] >>>> Sorry, I forgot to let you know that [PartCost] is set to Currency and >>>> [QtyPerBox] is set to Number - Long Integer. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.