Divide by Zero when it should not.

[=?ISO-8859-1?Q?Mathieu_Pag=E9?=]

Hi,

In Access 2003, I have a query that return a "Division by Zero" error
while it should not :

<code>
SELECT reqNbMotsAffectesParDetailParService.CodeDetail,
reqNbMotsAffectesParDetailParService.SommeDeNbMots,
reqNbMotsParDetailParService.Quantite,
IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Quantite]-[SommeDeNbMots])/[SommeDeNbMots])
AS Expr2
FROM reqNbMotsAffectesParDetailParService INNER JOIN
reqNbMotsParDetailParService ON
(reqNbMotsAffectesParDetailParService.CodeService =
reqNbMotsParDetailParService.CodeService) AND
(reqNbMotsAffectesParDetailParService.CodeDetail =
reqNbMotsParDetailParService.CodeDetail)
WHERE
(((IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Quantite]-[SommeDeNbMots])/[SommeDeNbMots]))>0));
</code>

It's a bit complex if you don't know what it supposed to do, so I'll
explain what it do. It takes the data from two queries, join them
together and compute a result (Expr2) based on some data coming from
both tables.

The computation involve an IIF with some division :

<code>
IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Quantite]-[SommeDeNbMots])/[SommeDeNbMots])
</code>

I already know that no mater what the condition evaluates to, both parts
of the IIF get evaluated. However, in the originals queries, "Quantite"
and "SommeDeNbMots" are never equal to zero, so there should be no problem.

Furthermore if I remove the WHERE clause, the query return a result
without any error.

Even more odds : If I change the condition in the where clause for ">1"
I get the division by zero error then the query stay in design mode, if
I change the condition for "<1" (Note the lesser than) I get the same
error, but the query is now in view mode with "#name" written in every
fields of the only visible row (Same behavior than with ">0").

Can someone help me with this problem?

Mathieu Pagé
(e-mail address removed)

 

[tina]

have you tried writing the query without the WHERE clause, to return all the
records with the calculated field included, then using that query as the
base for another query and setting the criteria on the calculated field from
the first query?

hth

 

[mathieu.page]

Hi hth,

Thanks for answering my post. I tried what you proposed, I get the same
error.



tina a écrit :

have you tried writing the query without the WHERE clause, to return all the
records with the calculated field included, then using that query as the
base for another query and setting the criteria on the calculated field from
the first query?

hth

Hi,

In Access 2003, I have a query that return a "Division by Zero" error
while it should not :

<code>
SELECT reqNbMotsAffectesParDetailParService.CodeDetail,
reqNbMotsAffectesParDetailParService.SommeDeNbMots,
reqNbMotsParDetailParService.Quantite,
IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Qua
ntite]-[SommeDeNbMots])/[SommeDeNbMots])
AS Expr2
FROM reqNbMotsAffectesParDetailParService INNER JOIN
reqNbMotsParDetailParService ON
(reqNbMotsAffectesParDetailParService.CodeService =
reqNbMotsParDetailParService.CodeService) AND
(reqNbMotsAffectesParDetailParService.CodeDetail =
reqNbMotsParDetailParService.CodeDetail)
WHERE
(((IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([
Quantite]-[SommeDeNbMots])/[SommeDeNbMots]))>0));
</code>

It's a bit complex if you don't know what it supposed to do, so I'll
explain what it do. It takes the data from two queries, join them
together and compute a result (Expr2) based on some data coming from
both tables.

The computation involve an IIF with some division :

<code>
IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Qua
ntite]-[SommeDeNbMots])/[SommeDeNbMots])
</code>

I already know that no mater what the condition evaluates to, both parts
of the IIF get evaluated. However, in the originals queries, "Quantite"
and "SommeDeNbMots" are never equal to zero, so there should be no problem.

Furthermore if I remove the WHERE clause, the query return a result
without any error.

Even more odds : If I change the condition in the where clause for ">1"
I get the division by zero error then the query stay in design mode, if
I change the condition for "<1" (Note the lesser than) I get the same
error, but the query is now in view mode with "#name" written in every
fields of the only visible row (Same behavior than with ">0").

Can someone help me with this problem?

Mathieu Pagé
(e-mail address removed)

 

[tina]

hmm, well, SQL is not my strongest point. all i can suggest is that you test
whether Access *thinks* you have zero values in your fields. try something
along the lines of

IIf([Quantite] = 0, "Q0", IIf([SommeDeNbMots] = 0, "S0",
IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Qua
ntite]-[SommeDeNbMots])/[SommeDeNbMots])))

if that doesn't shed any light, the only other WAG i can suggest is that you
try coercing the return value to a Double data type to make sure it
accommodates values less than 1, and/or you might try coercing the initial
values to Double data type. in either case, you can use the CDbl() function,
to change the data types within the query.

hth


Hi hth,

Thanks for answering my post. I tried what you proposed, I get the same
error.



tina a écrit :

have you tried writing the query without the WHERE clause, to return all the
records with the calculated field included, then using that query as the
base for another query and setting the criteria on the calculated field from
the first query?

hth

Hi,

In Access 2003, I have a query that return a "Division by Zero" error
while it should not :

<code>
SELECT reqNbMotsAffectesParDetailParService.CodeDetail,
reqNbMotsAffectesParDetailParService.SommeDeNbMots,
reqNbMotsParDetailParService.Quantite,

IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Qua

ntite]-[SommeDeNbMots])/[SommeDeNbMots])
AS Expr2
FROM reqNbMotsAffectesParDetailParService INNER JOIN
reqNbMotsParDetailParService ON
(reqNbMotsAffectesParDetailParService.CodeService =
reqNbMotsParDetailParService.CodeService) AND
(reqNbMotsAffectesParDetailParService.CodeDetail =
reqNbMotsParDetailParService.CodeDetail)
WHERE

(((IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([

Quantite]-[SommeDeNbMots])/[SommeDeNbMots]))>0));
</code>

It's a bit complex if you don't know what it supposed to do, so I'll
explain what it do. It takes the data from two queries, join them
together and compute a result (Expr2) based on some data coming from
both tables.

The computation involve an IIF with some division :

<code>

IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Qua

ntite]-[SommeDeNbMots])/[SommeDeNbMots])
</code>

I already know that no mater what the condition evaluates to, both parts
of the IIF get evaluated. However, in the originals queries, "Quantite"
and "SommeDeNbMots" are never equal to zero, so there should be no problem.

Furthermore if I remove the WHERE clause, the query return a result
without any error.

Even more odds : If I change the condition in the where clause for ">1"
I get the division by zero error then the query stay in design mode, if
I change the condition for "<1" (Note the lesser than) I get the same
error, but the query is now in view mode with "#name" written in every
fields of the only visible row (Same behavior than with ">0").

Can someone help me with this problem?

Mathieu Pagé
(e-mail address removed)