Removing Unwanted Decimal Places As A Result Of VBA Calculation, Access 2007

[Lateral]

Hi Guys,

I have an issue that I just can't resolve and I am sure there is a simple way to fix this.

I have a number of records display in a subform in Datasheet view.

I have a field called "Me.SupplierWSP" that is defined as Currency in the table.

The following VBA code is attached to the Double Click Event on "Me.SupplierWSP":



Private Sub SupplierWSP_DblClick(Cancel As Integer)



If Forms![Workorders].[Supplier] = True And IsNull(Me.[SupplierWSPDate]) Then

'Treat this as a Purchase Order

Me.Text56 = Me.SupplierWSP / Me.ExchangeRate

Me.[Wholesale_Price] = Me.Text56

Me.SupplierWSPDate = Now()

End If




Ok, firstly the above code works fine and updates the fields correctly EXCEPT, it stores the values with 4 decimal places.



For example, if Me.SupplierWSP = $1,200.00 and the Me.ExchangeRate is 0.61 (this is to convert USD to AUD), the result is $1967.2131. This is causing a problem when I use Me.SupplierWSP in a calculation.

For example, if I do the following:

10 x MeSupplierWSP

It does 10 x 1967.2131 = 19672.131 which is not what I want. I want it to be 1967.21.



Is there someway to acheive this result?

Thanks again for your help.

Cheers
Greg

 

[MrHans]

There is a function called Round() available, it's made to round an amount to a specified amount of decimals...

http://www.techonthenet.com/access/functions/numeric/round.php

 

[Lateral]

Thanks for the quick reply....I thought the Round function wasn't what I wanted?

I have since changed the code and it seems to be working as I want. Do you see any issues using this method?:

Private Sub SupplierWSP_DblClick(Cancel As Integer)

Dim strvalue As Currency

If Forms![Workorders].[Supplier] = True And IsNull(Me.[SupplierWSPDate]) Then



Me.Text56 = Me.SupplierWSP / Me.ExchangeRate

strvalue = Format(CCur(Me.Text56), "Currency")

Me.Text56 = strvalue


Me.[Wholesale_Price] = Me.Text56


Me.SupplierWSPDate = Now()

End If


End Sub

 

[arnelgp]

you can just simplify it to:

Me.Text56 = Format(Me.SupplierWSP / Me.ExchangeRate, "Currency")

 

[Lateral]

Thanks mate,

 

[Galaxiom]

For precise calculations, the Exchange rate should also be stored as Currency.

Currency stores and calculates using scaled integers and avoids the inherently imprecise nature of fractional calculations using Single or Double floating point numbers.