Where > 0

G

Geel

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal]) 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)
WHERE (((BenefitsTOperiode.PVtotal)>0))
GROUP BY Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID;

I've got an issue with dividing by 0. I'm actually calculating a
percentage for every record, if 1 of the total PV of total SAV is 0 the
outcome has to be 0 or n.a. or atleast work.

Anybody have a clue besides entering a different PV or SAV value then 0
(f.e. 0,0001)?

I tried fixing this by the above WHERE statement, but this way the
record won't show up at all and then because of the INNER JOIN in the
total query the complete record will not show up. So this ain't the
solution yet.
 
G

Geel

KARL DEWEY schreef:
Try this ---
IIF([BenefitsTOperiode].[PVtotal]) = 0, 0,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal])) AS
SumOfPVSum


Geel said:
SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal]) 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)
WHERE (((BenefitsTOperiode.PVtotal)>0))
GROUP BY Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID;

I've got an issue with dividing by 0. I'm actually calculating a
percentage for every record, if 1 of the total PV of total SAV is 0 the
outcome has to be 0 or n.a. or atleast work.

Anybody have a clue besides entering a different PV or SAV value then 0
(f.e. 0,0001)?

I tried fixing this by the above WHERE statement, but this way the
record won't show up at all and then because of the INNER JOIN in the
total query the complete record will not show up. So this ain't the
solution yet.
Click to expand...
Click to expand...

I tried this in a couple of different ways, but this 1 looks the most
working although it's not.

this is what i did:

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID, IIF([BenefitsTOperiode].[PVtotal]) = 0, 0,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal]) 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;
 
G

Geel

KARL DEWEY schreef:
You did not say what the results were - just that it did not work - kinda
hard to go from there.

Try Is Null instead of the = 0, 0, in the statement.

Geel said:
KARL DEWEY schreef:
Try this ---
IIF([BenefitsTOperiode].[PVtotal]) = 0, 0,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal])) AS
SumOfPVSum


:

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal]) 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)
WHERE (((BenefitsTOperiode.PVtotal)>0))
GROUP BY Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID;

I've got an issue with dividing by 0. I'm actually calculating a
percentage for every record, if 1 of the total PV of total SAV is 0 the
outcome has to be 0 or n.a. or atleast work.

Anybody have a clue besides entering a different PV or SAV value then 0
(f.e. 0,0001)?

I tried fixing this by the above WHERE statement, but this way the
record won't show up at all and then because of the INNER JOIN in the
total query the complete record will not show up. So this ain't the
solution yet.
Click to expand...

I tried this in a couple of different ways, but this 1 looks the most
working although it's not.

this is what i did:

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID, IIF([BenefitsTOperiode].[PVtotal]) = 0, 0,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal]) 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;
Click to expand...
Click to expand...

when i use

IIF([BenefitsTOperiode].[PVtotal]) = 0, 0,

it returns the error: Wrong number of arguments used in query
expression 'IIF([BenefitsTOperiode].[PVtotal]) = 0'.

when i use:

IIF([BenefitsTOperiode].[PVtotal]) = 0, IS NULL,

it returns the error: Syntax error (missing operator) in query
expression 'IS NULL'.

I've been looking at the CASE statement aswell, but aint got that to
work either. Thx for your time and help already.

Tim
 
G

Geel

Geel schreef:
KARL DEWEY schreef:
You did not say what the results were - just that it did not work - kinda
hard to go from there.

Try Is Null instead of the = 0, 0, in the statement.

Geel said:
KARL DEWEY schreef:

Try this ---
IIF([BenefitsTOperiode].[PVtotal]) = 0, 0,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal])) AS
SumOfPVSum


:

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal]) 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)
WHERE (((BenefitsTOperiode.PVtotal)>0))
GROUP BY Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID;

I've got an issue with dividing by 0. I'm actually calculating a
percentage for every record, if 1 of the total PV of total SAV is 0 the
outcome has to be 0 or n.a. or atleast work.

Anybody have a clue besides entering a different PV or SAV value then 0
(f.e. 0,0001)?

I tried fixing this by the above WHERE statement, but this way the
record won't show up at all and then because of the INNER JOIN in the
total query the complete record will not show up. So this ain't the
solution yet.



I tried this in a couple of different ways, but this 1 looks the most
working although it's not.

this is what i did:

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID, IIF([BenefitsTOperiode].[PVtotal]) = 0, 0,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal]) 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;
Click to expand...
Click to expand...

when i use

IIF([BenefitsTOperiode].[PVtotal]) = 0, 0,

it returns the error: Wrong number of arguments used in query
expression 'IIF([BenefitsTOperiode].[PVtotal]) = 0'.

when i use:

IIF([BenefitsTOperiode].[PVtotal]) = 0, IS NULL,

it returns the error: Syntax error (missing operator) in query
expression 'IS NULL'.

I've been looking at the CASE statement aswell, but aint got that to
work either. Thx for your time and help already.

Tim
Click to expand...

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;

This is my CASE workout. Wich isn't working either. Really hoping for
help, running out of options and read like all the information
available. Only real difference from my point of view is that in my
column selects i also define the table. But this shouldn't be a
problem?! but maybe i'm doing it wrong.
 
J

John Vinson

when i use

IIF([BenefitsTOperiode].[PVtotal]) = 0, 0,

it returns the error: Wrong number of arguments used in query
expression 'IIF([BenefitsTOperiode].[PVtotal]) = 0'.

when i use:

IIF([BenefitsTOperiode].[PVtotal]) = 0, IS NULL,

it returns the error: Syntax error (missing operator) in query
expression 'IS NULL'.

I've been looking at the CASE statement aswell, but aint got that to
work either. Thx for your time and help already.
Click to expand...

Access doesn't support the CASE statement except in passthrough
queries, so don't spend your time there! And an IIF statement can only
return a *value*, not an operator such as IS NULL.

Try using NZ to catch both zero and NULL values:

SELECT Contract_savings.Savings_ID, Contract_savings.Contract_ID,
Contract_savings.Opco_ID,
IIF(NZ(([BenefitsTOperiode].[PVtotal]) = 0, 0,
Sum([BenefitsTOSperiode].[SAVtotal]/[BenefitsTOperiode].[PVtotal]) 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;


John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Workaround: Divided by 0 error 2
Am I forcing the 0's correctly? 5
Access 2003: Problem with like in where clause for a recordset 1

Top