Workaround: Divided by 0 error

[Geel]

Hello, my access (form/table/query/report) system is completely working
and in it's testing phase, in this phase i ran into the dividing by 0
problem, wich a lot of you knows of aswell. There is a lot to read on
this subject, and i found out there are 2 capeable solutions. The IFF
statement, tried but didn't work for me, and the CASE wich I havn't
gotten to work either. I dunnot know if it's just me, or me? Since this
should be working but it isn't.

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID, (CASE [BenefitsTOSperiode].[SAVtotal] WHEN 0
THEN NULL ELSE [BenefitsTOSperiode].[SAVtotal] /
[BenefitsTOperiode].PVtotal END)* 100.00) AS SumOfPVSum
FROM BenefitsTOperiode INNER JOIN (BenefitsTOSperiode INNER JOIN
Contract_savings ON (BenefitsTOSperiode.Opco_ID =
Contract_savings.Opco_ID) AND (BenefitsTOSperiode.Contract_ID =
Contract_savings.Contract_ID)) ON (BenefitsTOperiode.Opco_ID =
Contract_savings.Opco_ID) AND (BenefitsTOperiode.Contract_ID =
Contract_savings.Contract_ID)
GROUP BY Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID;


error: Syntax error (missing operator) in query expression '(CASE
[BenefitsTOSperiode].[SAVtotal] WHEN 0
THEN NULL ELSE [BenefitsTOSperiode].[SAVtotal] /
[BenefitsTOperiode].PVtotal END)* 100.00)'.

Any ideas are very appreciated, trying to finish up my student intern
task and get a good degree

 

[Van T. Dinh]

Which Back-End are you using? JET or another database engine?

(Assuming you are using JET Back-End), the JET does not use the CASE
construct and you have to use IIf. Try something like:

========
SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID,
IIf( [BenefitsTOSperiode].[PVtotal] = 0, Null,
[BenefitsTOSperiode].[SAVtotal] / [BenefitsTOperiode].[PVtotal] *
100.00)
AS SumOfPVSum
FROM BenefitsTOperiode INNER JOIN
(BenefitsTOSperiode INNER JOIN Contract_savings
ON (BenefitsTOSperiode.Opco_ID = Contract_savings.Opco_ID)
AND (BenefitsTOSperiode.Contract_ID = Contract_savings.Contract_ID))
ON (BenefitsTOperiode.Opco_ID = Contract_savings.Opco_ID)
AND (BenefitsTOperiode.Contract_ID = Contract_savings.Contract_ID)
GROUP BY Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID;
========

 

[Geel]

Van T. Dinh schreef:

Which Back-End are you using? JET or another database engine?

(Assuming you are using JET Back-End), the JET does not use the CASE
construct and you have to use IIf. Try something like:

========
SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID,
IIf( [BenefitsTOSperiode].[PVtotal] = 0, Null,
[BenefitsTOSperiode].[SAVtotal] / [BenefitsTOperiode].[PVtotal] *
100.00)
AS SumOfPVSum
FROM BenefitsTOperiode INNER JOIN
(BenefitsTOSperiode INNER JOIN Contract_savings
ON (BenefitsTOSperiode.Opco_ID = Contract_savings.Opco_ID)
AND (BenefitsTOSperiode.Contract_ID = Contract_savings.Contract_ID))
ON (BenefitsTOperiode.Opco_ID = Contract_savings.Opco_ID)
AND (BenefitsTOperiode.Contract_ID = Contract_savings.Contract_ID)
GROUP BY Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID;
========


--
HTH
Van T. Dinh
MVP (Access)

Hello, my access (form/table/query/report) system is completely working
and in it's testing phase, in this phase i ran into the dividing by 0
problem, wich a lot of you knows of aswell. There is a lot to read on
this subject, and i found out there are 2 capeable solutions. The IFF
statement, tried but didn't work for me, and the CASE wich I havn't
gotten to work either. I dunnot know if it's just me, or me? Since this
should be working but it isn't.

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID, (CASE [BenefitsTOSperiode].[SAVtotal] WHEN 0
THEN NULL ELSE [BenefitsTOSperiode].[SAVtotal] /
[BenefitsTOperiode].PVtotal END)* 100.00) AS SumOfPVSum
FROM BenefitsTOperiode INNER JOIN (BenefitsTOSperiode INNER JOIN
Contract_savings ON (BenefitsTOSperiode.Opco_ID =
Contract_savings.Opco_ID) AND (BenefitsTOSperiode.Contract_ID =
Contract_savings.Contract_ID)) ON (BenefitsTOperiode.Opco_ID =
Contract_savings.Opco_ID) AND (BenefitsTOperiode.Contract_ID =
Contract_savings.Contract_ID)
GROUP BY Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID;


error: Syntax error (missing operator) in query expression '(CASE
[BenefitsTOSperiode].[SAVtotal] WHEN 0
THEN NULL ELSE [BenefitsTOSperiode].[SAVtotal] /
[BenefitsTOperiode].PVtotal END)* 100.00)'.

Any ideas are very appreciated, trying to finish up my student intern
task and get a good degree

Yess, thanks a lot i nailed it, doing the following:

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID, Sum(IIf([BenefitsTOperiode].[PVtotal] = 0,
Null, [BenefitsTOSperiode].[SAVtotal] / [BenefitsTOperiode].[PVtotal] *
100.00)) AS SumOfPVSum

Thanks a lot.

Tim