Calculation in VB Code of Currency off by cents

[DB2010MN26]

I'm performing a calculation in the after update event of a datasheet form which I'm getting an answer about 20 cents off. Do I need to convert the numbers to something else in my subroutine?



actual table values:
costbasisunitprice = 12.38
CurrentPrice = 11.49
TradeAmt = 59399.03

(CurrentPrice - costbasisunitprice) * (TradeAmt / CurrentPrice) = 4600.97

The answer I get with my code = 4600.77

my code:

Private Sub TradeAmt_AfterUpdate()
[TradeTax] = ([CurrentPrice] - [costbasisunitprice]) * ([TradeAmt] / [CurrentPrice])
End Sub

[alansidman]

When I do the calculation manually, I get 4600.99 so I am guessing that this is a rounding or truncation issue somewhere in your db. What are your fields formatted as? Numbers? Long Integer, Single, Double, Decimal? This will make a difference possibly

[orange]

Try the following sample using your figures (I get -4600.96925152307 )

Sub DecimalPlaceIssues()
Dim costbasisunitprice As Double
Dim CurrentPrice As Double
Dim TradeAmt As Double
Dim TradeTax As Double
costbasisunitprice = 12.38
CurrentPrice = 11.49
TradeAmt = 59399.03
'
'(CurrentPrice - costbasisunitprice) * (TradeAmt / CurrentPrice) = 4600.97'
'
'The answer I get with my code = 4600.77

myCode:
[TradeTax] = ([CurrentPrice] - [costbasisunitprice]) * ([TradeAmt] / [CurrentPrice])
Debug.Print TradeTax
End Sub

[John_G]

Something you have given is not right - your result should be negative, since (CurrentPrice - costbasisunitprice) = -.89, making the whole expression negative (-4600.97).

Try putting a few debug.print statements into your subroutine to verify the values are actually what you think they should be. You could also try using the Val() function with each of your fields, to ensure they really are numeric (e.g. Val([CurrentPrice]) )

HTH

John

[pbaldy]

That should work. My first guess is that one or more of those numbers is actually something else in the data, and you're only seeing that. One way to test:

[TradeTax] = (Round([CurrentPrice],2) - Round([costbasisunitprice],2)) * (Round([TradeAmt],2) / Round([CurrentPrice],2))

[DB2010MN26]

Something you have given is not right - your result should be negative, since (CurrentPrice - costbasisunitprice) = -.89, making the whole expression negative (-4600.97).
John

Sorry about that, my answer was negotive, just off by the cents.

I tryed the Val( function but still got the same answer. Looks like the issue was rounding since I was able to get (-4600.97) when I used pbaldy's example:


[TradeTax] = (Round([CurrentPrice],2) - Round([costbasisunitprice],2)) * (Round([TradeAmt],2) / Round([CurrentPrice],2))

Also for reference, my tables had the data formated as currency with decimal places set to 2.

Thanks everyone for your help and fast response!

[orange]

Sorry about that, my answer was negotive, just off by the cents.

I tryed the Val( function but still got the same answer. Looks like the issue was rounding since I was able to get (-4600.97) when I used pbaldy's example:


[TradeTax] = (Round([CurrentPrice],2) - Round([costbasisunitprice],2)) * (Round([TradeAmt],2) / Round([CurrentPrice],2))

Also for reference, my tables had the data formated as currency with decimal places set to 2.

Thanks everyone for your help and fast response!

I'd use more that 2 decimal places for the intermediate calculations.
Use Double data type, and report in Currency if that's what you need.

[pbaldy]

My suggestion was intended to identify the problem, which appears to be the values in the table. In other words, you may be seeing

CurrentPrice = 11.49

but what's actually in the table is

CurrentPrice = 11.493

Depending on your specific needs, you may want to round values as they are entered into the table to prevent this type of thing in the future.