SQL code

  • Thread starter Thread starter Armin
  • Start date Start date
A

Armin

What's wrong with this SQL code? It should return programs
to which donor hasn't donated.

SELECT tblprog.Program, tblcontrib.Program
FROM tblprog LEFT JOIN tblcontrib ON tblprog.Program =
tblcontrib.Program
WHERE (((tblcontrib.Program) Is Null) AND
((tblcontrib.DonorID)=[Forms]![frmdonors]![DonorID]));


Thanks in advance

Armin
 
Hi Armin,
because off my bad english I don't understand your
question,
It should return programs to which donor hasn't donated.
but

WHERE (((tblcontrib.Program) Is Null) AND
((tblcontrib.DonorID)=[Forms]![frmdonors]![DonorID]));
Click to expand...

if you ask for null you should use
isnull(tblcontrib.Program)
and
if there was'nt a record linked to the tblprog, probally
your DonorID is null too.

Hope it helps.
Niels
 
What's wrong with this SQL code? It should return programs
to which donor hasn't donated.

SELECT tblprog.Program, tblcontrib.Program
FROM tblprog LEFT JOIN tblcontrib ON tblprog.Program =
tblcontrib.Program
WHERE (((tblcontrib.Program) Is Null) AND
((tblcontrib.DonorID)=[Forms]![frmdonors]![DonorID]));
Click to expand...

It won't, because you're selecting those records where there is
nothing in tblContrib and where that nonexistant record's nonexistant
value of DonorID is equal to the forms reference!

Use the Exists clause instead:

SELECT tblProgram
FROM tblProgram
WHERE NOT EXISTS
(SELECT Program FROM tblcontrib WHERE tblcontrib.Program =
tblProgram.Program AND tblcontrib.DonorID = Forms!frmDonors!DonorID;)
 
Back
Top