Problems in calculations in MS Access 2003

[migueltxa]

Hi all,

I have a problem with some calculations in MS Access 2003. I have 3
fields (Qty, Price and VolumeUSD) in a form and the following formula:
VolumeUSD = Qty * Price.

Qty and VolumeUSD are formated as "Standard" and Price as "General
Number".

Example: Qty = 4,400 ; Price = 39.1602

When all 3 fields are "Single", VolumeUSD = 172,308.90 (wrong)!
BUT, when all 3 fields "Double", VolumeUSD = 172,308.88 (correct)

I have much more situations where I get errors in calculations if I use
the variables as "Single".

CAN SOMEONE EXPLAIN ME THIS ?!

In the Database I have about 4000 records! Until now the only way I can
imagine to fix this problem is to change in the Table Design all three
variables from Single to Double, but if I do this the Price will change
to 39,1601093458574357 instead of 39,1602! This means that I will have
to input again the Price for each of the 4000 records!!! This will take
a lot of time!

THERE'S ANY OTHER WAY TO FIX THIS PROBLEM IN THE DATABASE IN A MUCH
FASTER WAY ?

Urgent!!!

Thanks, Miguel Teixeira.

[loringdo]

With many of the reports I created in Access there is a problem of
rounding off. But I can live with that! Its usually no more then a
dollar difference. Reports that have large Sums I declare a variable as
variant. Then use either the Format Currency Function or Format
Function, then set total sum how many decimal places. Have to be
consistence with variables. You can use module function for every
calculation needed. That way all the changes also will be consistence.
I don't think I would use Single for this type of calculation. Even
though the Qty I would probadly use an integer. Hope this help you.
Dim varSum As Variant

varSum = Format(varSum, "$##.#0")

'FormatCurrency Function

varSum = FormatCurrency(varSum, 2, vbTrue, vbTrue, vbUseDefault)

'FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit
,UseParensForNegativeNumbers [,GroupDigits]]]])
Also make sure from the table properites, textbox propertes on forms
and query properties have the same datatype also, its good ideal to
check. I don't think you have to reenter the data to change how the
data is view, you should be able to change for instance from a number
to currency.

loringdo

[loringdo]

I forgot one more thing.

Public sub pViewTheAns()

Dim mQty as integer
Dim mVol as variant
Dim mPrice as Currency

mQty = 100
mPrice = 45.36

mVol = FormatCurrency(mQty * mPrice,2, vbTrue, vbTrue, vbUseDefault)

Debug.Print mVol

End Sub

[loringdo]

I forgot one more thing.

Public sub pViewTheAns()

Dim mQty as integer
Dim mVol as variant
Dim mPrice as Currency

mQty = 100
mPrice = 45.36

mVol = FormatCurrency(mQty * mPrice,2, vbTrue, vbTrue, vbUseDefault)

Debug.Print mVol

End Sub

[loringdo]

I forgot one more thing.

Public sub pViewTheAns()

Dim mQty as integer
Dim mVol as variant
Dim mPrice as Currency

mQty = 100
mPrice = 45.36

mVol = FormatCurrency(mQty * mPrice,2, vbTrue, vbTrue, vbUseDefault)

Debug.Print mVol

End Sub

[migueltxa]

Thanks a lot! Extremely helpful.

[mike]

On 21 Oct 2005 08:57:37 -0700, mi*******@gmail.com wrote:

[snip]

Qty and VolumeUSD are formated as "Standard" and Price as "General
Number".

Example: Qty = 4,400 ; Price = 39.1602

When all 3 fields are "Single", VolumeUSD = 172,308.90 (wrong)!
BUT, when all 3 fields "Double", VolumeUSD = 172,308.88 (correct)

I have much more situations where I get errors in calculations if I use
the variables as "Single".

CAN SOMEONE EXPLAIN ME THIS ?!

Yes. You're using the wrong data type. "Format" has to do with how
values look, not with what the values are. You can format '1.23456789'
to look like '1.23', but that doesn't change its value.

You need to use the Currency or Decimal data type for [Price] and
[VolumeUSD]. If you need fractional quantities, use Currency or
Decimal for [Qty]. Otherwise, use Integer or Long Integer. Read
Access Help first.

You probably also need to round the right value to the right number of
decimal places at the right time. "Right value", "right number of
decimal places", and "right time" are application-dependent.

Don't change the data type of your existing columns. Add new columns,
get the right values into the new columns, then delete the old ones and
rename the new ones.

--
Mike Sherrill
Information Management Systems