Divide by Zero and confused

[Gaetanm via AccessMonster.com]

I have a calculation in a query that gives me percentage but I come up with
#error in the
percentage field at times.

Percent: IIf([basesellingprice]-[taxinclusiveunitprice]=0,0,[basesellingprice]
-[taxinclusiveunitprice])/([taxinclusiveunitprice])*100


Basesellingprice taxinclusiveunitprice
.03 0 =
#error

In my Query I created a test column called check and this is the result:

check: ([basesellingPrice]-[taxinclusiveunitprice])

.03 - 0 = .025
The .025 is on the same record as #error

The basesellingprice of .03 is actual .03 and not a rounded number

Any ideas on the #error and the .025 issue

Gaetanm

 

[Marshall Barton]

I have a calculation in a query that gives me percentage but I come up with
#error in the
percentage field at times.

Percent: IIf([basesellingprice]-[taxinclusiveunitprice]=0,0,[basesellingprice]
-[taxinclusiveunitprice])/([taxinclusiveunitprice])*100


Basesellingprice taxinclusiveunitprice
.03 0 =
#error

In my Query I created a test column called check and this is the result:

check: ([basesellingPrice]-[taxinclusiveunitprice])

.03 - 0 = .025
The .025 is on the same record as #error

The basesellingprice of .03 is actual .03 and not a rounded number

Any ideas on the #error and the .025 issue

The error is because you are testing the numerator instead
of the denominator.

Percent:
IIf(taxinclusiveunitprice=0,0,(basesellingprice-taxinclusiveunitprice)/taxinclusiveunitprice)*100

Your .25 issue is somewhat strange. If the basesellingPrice
field is a Single or Double type field, then .3 is NOT an
exact value as can be seen by Int(CDbl(.3)*10) returning 2.
I would have expected the rounding error to be too small to
show up in your simple calculation though.

If basesellingPrice field is a Currency field, then you
should not see this problem.

 

[Gaetanm via AccessMonster.com]

Marshall

Thanks for showing me the tree in the forest of numerators and denominators.
I checked
on that [baselinepricing]. This field is a linked field to a canned
accounting package and
sure enough its a bouble and not currency.

Thanks again

Gaetanm

I have a calculation in a query that gives me percentage but I come up with
#error in the

[quoted text clipped - 17 lines]

Any ideas on the #error and the .025 issue

The error is because you are testing the numerator instead
of the denominator.

Percent:
IIf(taxinclusiveunitprice=0,0,(basesellingprice-taxinclusiveunitprice)/taxinclusiveunitprice)*100

Your .25 issue is somewhat strange. If the basesellingPrice
field is a Single or Double type field, then .3 is NOT an
exact value as can be seen by Int(CDbl(.3)*10) returning 2.
I would have expected the rounding error to be too small to
show up in your simple calculation though.

If basesellingPrice field is a Currency field, then you
should not see this problem.

 

[Marshall Barton]

I suspect that your accounting program really has a value of
,025, but when you checked it, it was rounded by something
to .03

Note that Access uses "Bankers Rounding" that rounds .025 to
..02 For Access to round .025 to .03 the real value would
have to be at least a small amount greater than .025
--
Marsh
MVP [MS Access]

Thanks for showing me the tree in the forest of numerators and denominators.
I checked
on that [baselinepricing]. This field is a linked field to a canned
accounting package and
sure enough its a bouble and not currency.

I have a calculation in a query that gives me percentage but I come up with
#error in the

[quoted text clipped - 17 lines]

Any ideas on the #error and the .025 issue

The error is because you are testing the numerator instead
of the denominator.

Percent:
IIf(taxinclusiveunitprice=0,0,(basesellingprice-taxinclusiveunitprice)/taxinclusiveunitprice)*100

Your .25 issue is somewhat strange. If the basesellingPrice
field is a Single or Double type field, then .3 is NOT an
exact value as can be seen by Int(CDbl(.3)*10) returning 2.
I would have expected the rounding error to be too small to
show up in your simple calculation though.

If basesellingPrice field is a Currency field, then you
should not see this problem.