calculations not coming out right... ASAP!

[stephendeloach via AccessMonster.com]

i have a form that does not multiply correctly. i enter Quantity, UnitPrice,
check Expendable (if needed and this adds 10% to the UnitPrice). then the
UnitPrice and the 10% are added together.. everything is fine. I then
multiple the UnitPrice (which goes to NetAmount) by the Quantity which is
then put in TotalAmount but is off by roughly 2 cents. It only happends when
i check the Expendable box. Here are my formulas....


Private Sub expen_AfterUpdate()
expen = expen / 100
End Sub

Private Sub Expendable_AfterUpdate()
If Me!Expendable = True Then
Me!Netamount = Nz(Me!UnitPrice, 0) * [expen] + [UnitPrice]
Else
Me!NetAmount = Nz(Me!Netamount, 0) - [expen] * [UnitPrice]
End If
End Sub

Private Sub test_AfterUpdate()
NetAmount = UnitPrice
End Sub

Private Sub test_Enter()
NetAmount = UnitPrice
End Sub


something isnt adding up right. i have tried everything and cant get it....
UnitPrice is set to Currency. Quantity is set to Number. Netamount is set to
Currency. Expen is set to Number (its the percent that is multiplied)... i
think thats all the info you should need....? Thanks

 

[Ken Snell \(MVP\)]

Your code likely sees [expen] as an integer type, and thus that may affect
the calculation.

Try this:

Private Sub Expendable_AfterUpdate()
If Me!Expendable = True Then
Me!Netamount = Nz(Me!UnitPrice, 0) * CCur([expen]) + [UnitPrice]
Else
Me!NetAmount = Nz(Me!Netamount, 0) - CCur([expen]) * [UnitPrice]
End If
End Sub


or

Private Sub Expendable_AfterUpdate()
If Me!Expendable = True Then
Me!Netamount = Nz(Me!UnitPrice, 0) * CSgl([expen]) + [UnitPrice]
Else
Me!NetAmount = Nz(Me!Netamount, 0) - CSgl([expen]) * [UnitPrice]
End If
End Sub

 

[stephendeloach via AccessMonster.com]

I tried both and none work.. it still says 170.86 * 5 = 854.32... not 854.30..
..

Your code likely sees [expen] as an integer type, and thus that may affect
the calculation.

Try this:

Private Sub Expendable_AfterUpdate()
If Me!Expendable = True Then
Me!Netamount = Nz(Me!UnitPrice, 0) * CCur([expen]) + [UnitPrice]
Else
Me!NetAmount = Nz(Me!Netamount, 0) - CCur([expen]) * [UnitPrice]
End If
End Sub

or

Private Sub Expendable_AfterUpdate()
If Me!Expendable = True Then
Me!Netamount = Nz(Me!UnitPrice, 0) * CSgl([expen]) + [UnitPrice]
Else
Me!NetAmount = Nz(Me!Netamount, 0) - CSgl([expen]) * [UnitPrice]
End If
End Sub

i have a form that does not multiply correctly. i enter Quantity,
UnitPrice,

[quoted text clipped - 31 lines]

Currency. Expen is set to Number (its the percent that is multiplied)... i
think thats all the info you should need....? Thanks

 

[Ken Snell \(MVP\)]

What data type is UnitPrice? Is it possible that its actual value is not
170.86, but instead is a number slightly larger (e.g., 170.864)? Be careful
that your Format property or Decimal Places property is not "fooling" you
about the actual value of the field/control because one of those properties
is "rounding" the data for the display.
--

Ken Snell
<MS ACCESS MVP>

I tried both and none work.. it still says 170.86 * 5 = 854.32... not
854.30..
.

Your code likely sees [expen] as an integer type, and thus that may affect
the calculation.

Try this:

Private Sub Expendable_AfterUpdate()
If Me!Expendable = True Then
Me!Netamount = Nz(Me!UnitPrice, 0) * CCur([expen]) + [UnitPrice]
Else
Me!NetAmount = Nz(Me!Netamount, 0) - CCur([expen]) * [UnitPrice]
End If
End Sub

or

Private Sub Expendable_AfterUpdate()
If Me!Expendable = True Then
Me!Netamount = Nz(Me!UnitPrice, 0) * CSgl([expen]) + [UnitPrice]
Else
Me!NetAmount = Nz(Me!Netamount, 0) - CSgl([expen]) * [UnitPrice]
End If
End Sub

i have a form that does not multiply correctly. i enter Quantity,
UnitPrice,

[quoted text clipped - 31 lines]

Currency. Expen is set to Number (its the percent that is multiplied)...
i
think thats all the info you should need....? Thanks

 

[stephendeloach via AccessMonster.com]

Currency decimal places set to auto... would that make a difference?

What data type is UnitPrice? Is it possible that its actual value is not
170.86, but instead is a number slightly larger (e.g., 170.864)? Be careful
that your Format property or Decimal Places property is not "fooling" you
about the actual value of the field/control because one of those properties
is "rounding" the data for the display.

I tried both and none work.. it still says 170.86 * 5 = 854.32... not
854.30..

[quoted text clipped - 29 lines]

 

[Ken Snell \(MVP\)]

Yes. Auto for Currency means two decimal places will be displayed, but it
will not round to just two decimal places. Currency data type stores four
decimal places accurately, so your 170.86 number most likely is being stored
as 170.864 as a result of how it was entered or calculated. (You can test
this by changing the decimal places property to 4 and see what the number
actually is.)

If you want the UnitPrice field to be accurate to just the two decimal
places, you will need to round it to just two decimal places before you
store it.
--

Ken Snell
<MS ACCESS MVP>

Currency decimal places set to auto... would that make a difference?

What data type is UnitPrice? Is it possible that its actual value is not
170.86, but instead is a number slightly larger (e.g., 170.864)? Be
careful
that your Format property or Decimal Places property is not "fooling" you
about the actual value of the field/control because one of those
properties
is "rounding" the data for the display.

I tried both and none work.. it still says 170.86 * 5 = 854.32... not
854.30..

[quoted text clipped - 29 lines]

i
think thats all the info you should need....? Thanks

 

[stephendeloach via AccessMonster.com]

I changed everything to 4 decimal points and it goes down to .3150 and it
should be .3000. Could it have something to do with the 10% that is being
added to it? I know it dosent sound like a big deal but we work with large
numbers and it could add up over time..... thanks for the help

Yes. Auto for Currency means two decimal places will be displayed, but it
will not round to just two decimal places. Currency data type stores four
decimal places accurately, so your 170.86 number most likely is being stored
as 170.864 as a result of how it was entered or calculated. (You can test
this by changing the decimal places property to 4 and see what the number
actually is.)

If you want the UnitPrice field to be accurate to just the two decimal
places, you will need to round it to just two decimal places before you
store it.

Currency decimal places set to auto... would that make a difference?

[quoted text clipped - 10 lines]

 

[Ken Snell \(MVP\)]

I changed everything to 4 decimal points and it goes down to .3150 and it
should be .3000. Could it have something to do with the 10% that is being
added to it? I know it dosent sound like a big deal but we work with large
numbers and it could add up over time..... thanks for the help

Unless you are specifically rounding a calculated number to just two decimal
places before you save the calculated value to the field, then, yes, you can
see 'number creep' over time.

 

[stephendeloach via AccessMonster.com]

But it is only with this particular one.. there are line items under this one
that all add up the same?

 

[Ken Snell \(MVP\)]

I don't know the history of your data, nor of how your database calculates
these values. So I cannot provide any more information about why this
particular value is not what you expected it to be?

 

[stephendeloach via AccessMonster.com]

i have had someone tell me to use the CInt command... but i cant figure out
where? when i add it in the form all of the numbers come out to .00 does
that help any? thanks

I don't know the history of your data, nor of how your database calculates
these values. So I cannot provide any more information about why this
particular value is not what you expected it to be?

But it is only with this particular one.. there are line items under this
one

[quoted text clipped - 12 lines]

 

[Ken Snell \(MVP\)]

CInt function will convert your number to an Integer value -- no decimal
value at all, meaning you'll lose everything to the right of the decimal
point, and it will round to nearest whole number. And the range of values
that an Integer can have is -32,768 to 32,767.
--

Ken Snell
<MS ACCESS MVP>

i have had someone tell me to use the CInt command... but i cant figure out
where? when i add it in the form all of the numbers come out to .00 does
that help any? thanks

I don't know the history of your data, nor of how your database calculates
these values. So I cannot provide any more information about why this
particular value is not what you expected it to be?

But it is only with this particular one.. there are line items under
this
one

[quoted text clipped - 12 lines]

can
see 'number creep' over time.