Solved Calculate and Store Number-Doubles with two Decimal places Only (1 Viewer)

[Sha]

Hello Everyone,

I am trying to create Sales and Collection System. I have number fields stored as Double. Most of these are calculated in the form level.
In the after update event of the Gross Sales, Taxable Sales, Vat , Withholding Tax and Net of EWT are calculated but I want it to store only the value rounded to two decimal places instead of the exact calculated number.

Gross Sales: Input

Calculations:

Taxable Sales: Gross Sales/(1.12)
Vat: Taxable Sales * 12%
EWT: Taxable Sales * Tax Rate
Net of EWT: Gross Sales - EWT

Hope you could help me with this. Thank you !

 

[Sha]

Silly me, I forgot the round function Now it's all good.

 

[The_Doc_Man]

Just remember that the ROUND function uses Banker's Rounding. Look up here:

Round function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[plog]

You shouldn't store calculated values. From all the fields you listed above only [Gross Sales] and [Tax Rate] should be fields in tables. The rest should live in a query where you do all those calculations and apply the Round() function.

 

[Pat Hartman]

Also, instead of using double to store currency, try using the currency field. It will avoid rounding errors. I use it for any number I use in calculations as long as I never need more than 4 decimal digits. Don't confuse the currency data type with the currency format. They are different. Currency data type fields can be formatted as you would format any number.