Round to 3rd decimal place and trim excess. 2

[JferWfer]

Okay, I know that the type of calculation I am about to explain may seem idiotic. However, due to the procedures currently in place I must make this work, any help would be greatly appreciated.

I have a query that takes a price per/lb subtracts a discount percentage and then multiplies by the total lbs. Simple right? Here's the catch, the company rounds the discounted pricing to the 100th decimal place and then multiplies from the rounded number.

Example of Problem:
Price per/lb: 1.295 minus Discount: 7.5% = 1.197875
Database multiplies the total lbs by the 1.197875 amount.
Company multiplies the total lbs by 1.198 amount.
Lets say total lbs is 1900
Database Price: $2275.96
Company Price: $2276.20 (and they don't want to change their procedure)

I need to somehow store the 1.198 and not 1.197875. Even though I have changed the formatting, Access still holds all
of the decimal places and uses them for calculating. Any
suggestions?

Thanks,
Jennifer

 

[PaulBricker]

Jennifer, if you have A2K or new you can use the Round() function.
Round(1.295 - (1.295*0.075),3)*1900

The result is 2276.20

The Round Function has two arguments, the expression and the number of places you want to round to.

Let me know if you have any issues.

Paul

 

[Hap007]

Jenifer,

If you have Access 2K and the round function, then Paul's suggestion should work.

But, when you deal with Integer, Long and Currency all in one calculation, sometimes access still gives you some strange results. Since your data will be more than likely defined that way Access 2K may not work either. your final answer may look like 2276.1999 even if you specify currency.

If you are using Access 97, then the round function does not exist.

Try this sample, it will work:
Just cut and paste into a command0 button and give it a try.

Private Sub Command0_Click()
Dim Answer As Currency
Dim PriceLB As Single
Dim OurWeight As Long
Dim tmpOurDiscount As Single
Dim OurDiscount As Single

PriceLB = 1.295
OurWeight = 1900
tmpOurDiscount = (PriceLB * (7.5 / 100))
OurDiscount = PriceLB - Val(Format(tmpOurDiscount, "#.###&quot)
Answer = Val(Format((OurWeight * OurDiscount), "#.##&quot)

MsgBox "OurDiscount =" & OurDiscount & " Answer=" & Answer

End Sub

Hope This Helps,
Hap
Access Add-on developer

http://www.fcs-software.com

-

 

[JferWfer]

Thanks!

 

[raskew]

In Access 97 you could something like this:

Function xRound(pnum As Double, pDisc As Double, factor As Double)
   xRound = Int((pnum * pDisc) * factor + 0.5) / factor
End Function
[code]
Which, using your example, could be called from the debug window with:
? xround(1.295, 0.925, 1000)
 1.198 
 
? xround(1.295, 0.925, 1000)* 1900
 2276.2 

? format(xround(1.295, 0.925, 1000)* 1900, "currency")
$2,276.20