Strange calculation

[mbr96 via AccessMonster.com]

I need to populate a control on a form with the product of two other values
on the same form. I use a macro with a SetValue statement to do this, but am
getting a strange result on the calculation.

Rent = Cost*LeaseRate

Cost = $15615
LeaseRate = .0278

When I run the Setvalue macro, it results in Rent = $434.090 (I used the
Number, Decimal data type for all three numerical values).

Pull out a calculator. $15615*.0278 = 434.097

What????? Access is getting $434.090, correct value is $434.097 which would
round to $434.10.

This isn't just formatting or rounding, what's going on?

If you need more specifics, please let me know.

MBR

 

[Ken Snell \(MVP\)]

Change all three fields to Currency data type instead of Number (Decimal
field size). Currency will give you more accurate fractional numbers;
Decimal will give you less accurate numbers (it's a floating point number
type) and sometimes can cause some buggy behavior.

 

[mbr96 via AccessMonster.com]

Here's a little more info - I created a table with two number, Decimal fields
to hold the 15615 and .0278. I then built a query on that table with an
expression that verified that the product of the two numbers comes out as 434.
097. This would seem to indicate something is happening on the form only???

MBR

 

[mbr96 via AccessMonster.com]

Here I am replying to myself again - looks like I've resolved this by simply
creating a control set to the product of the two other fields. Setting the
value through a macro gives an incorrect value for some reason I can't figure
out, so this works fine.

Thanks for any consideration. This site is very informative!

MBR

Here's a little more info - I created a table with two number, Decimal fields
to hold the 15615 and .0278. I then built a query on that table with an
expression that verified that the product of the two numbers comes out as 434.
097. This would seem to indicate something is happening on the form only???

MBR

I need to populate a control on a form with the product of two other values
on the same form. I use a macro with a SetValue statement to do this, but am

[quoted text clipped - 18 lines]

MBR

 

[Ken Snell \(MVP\)]

Probably was caused by intermediate calculation / storage of the floating
point numbers prior to multiplying them together. This can lead to
variations.

--

Ken Snell
<MS ACCESS MVP>

Here I am replying to myself again - looks like I've resolved this by
simply
creating a control set to the product of the two other fields. Setting
the
value through a macro gives an incorrect value for some reason I can't
figure
out, so this works fine.

Thanks for any consideration. This site is very informative!

MBR

Here's a little more info - I created a table with two number, Decimal
fields
to hold the 15615 and .0278. I then built a query on that table with an
expression that verified that the product of the two numbers comes out as
434.
097. This would seem to indicate something is happening on the form
only???

MBR

I need to populate a control on a form with the product of two other
values
on the same form. I use a macro with a SetValue statement to do this,
but am

[quoted text clipped - 18 lines]

MBR

 

[mbr96 via AccessMonster.com]

One more time - as soon as I try to store this value, it sets back to 434.090.
Starting to pull my hair out.

I've looked at the scale, precision and decimal places fields for each of the
number (decimal) data types, and they're all adequate to hold enough decimals.


Very confused. Any help please??

Mbr

Here I am replying to myself again - looks like I've resolved this by simply
creating a control set to the product of the two other fields. Setting the
value through a macro gives an incorrect value for some reason I can't figure
out, so this works fine.

Thanks for any consideration. This site is very informative!

MBR

Here's a little more info - I created a table with two number, Decimal fields
to hold the 15615 and .0278. I then built a query on that table with an

[quoted text clipped - 8 lines]

 

[Ken Snell \(MVP\)]

See my first reply to you in this thread....

 

[Jamie Collins]

Change all three fields toCurrencydata type instead of Number (Decimal
field size).Currency will give you more accurate fractional numbers; Decimal will give you less accurate numbers (it's a floating point number type)

Incorrect. Jet's DECIMAL type is a fixed point numeric type (not
floating point) and can have a decimal scale much greater than
CURRENCY's (which is always four).

and sometimes can cause some buggy behavior.

Seriously, if we applied that criteria to every Access/Jet feature
then they'd be nothing left to use. Try being more specific, please

Note that DECIMAL is Jet's native type for values within a certain
range e.g.

SELECT TYPENAME(3000000000), TYPENAME(0.5)

both return 'Decimal'. So if 'buggy behavior' really is a problem then
the product is not fit for purpose!

Jamie.

--

 

[Jamie Collins]

See my first reply to you in this thread....

Why, have you been able to reproduce the problem? I haven't and I'd
hesitate in prescribing a solution involving altering the design of
the table until I had.

FWIW I've seen similar errors where values with some characteristics
get truncated using ADO recordsets with certain cursor types (although
this doesn't appear to fit the OP's scenario) e.g.

Sub DecTrucErr()
Kill Environ$("temp") & "\DropMe.mdb"
Dim rs
Dim cat
Set cat = CreateObject("ADOX.Catalog")
cat.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
Set rs = cat.ActiveConnection.Execute( _
"SELECT 1.1 / 10;")
MsgBox rs.GetString
' Returns 0.11 -- correct
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
cat.ActiveConnection.ConnectionString
.CursorLocation = 3
.Open
Set rs = .Execute( _
"SELECT 1.1 / 10;")
MsgBox rs.GetString
' Returns 0.1 -- incorrect, truncated
End With
End Sub

Jamie.

--