query change

[Guest]

I have the following query as the basis for a multiselect listbox:

SELECT DISTINCT FY02_BASETABLE.Program, Program.Obligation
FROM Program LEFT JOIN FY02_BASETABLE ON Program.Obligation =
FY02_BASETABLE.Program
WHERE (((FY02_BASETABLE.FY)=[forms]![password]![fy]))
ORDER BY FY02_BASETABLE.Program;

As you can probably tell, the preceding query retrieves all programs from
the program table and all programs from the fy02 basetable so as long as the
program names reside in both tables. However, I would like to change the
preceding query so that program names which are in the program table, but not
in the fy02 basetable are retrieved. What do I need to do to make this
happen? I tried to change the "=" to "<>" but to no avail.

Thanks in advance,
geebee

 

[Guest]

try WHERE (((FY02_BASETABLE.FY)=is null))

 

[Guest]

sorry
try WHERE (((FY02_BASETABLE.FY)=null))

 

[John Spencer (MVP)]

IF you only want the records from PROGRAM that don't have a corresponding record
or records in FY02_BaseTable then try the following

SELECT DISTINCT FY02_BASETABLE.Program, Program.Obligation
FROM Program LEFT JOIN FY02_BASETABLE
ON Program.Obligation = FY02_BASETABLE.Program
WHERE FY02_BASETABLE.Program IS NULL
ORDER BY FY02_BASETABLE.Program;

AND the ORDER BY clause really does nothing in this case since all the values
for Program are null.

sorry
try WHERE (((FY02_BASETABLE.FY)=null))

I have the following query as the basis for a multiselect listbox:

SELECT DISTINCT FY02_BASETABLE.Program, Program.Obligation
FROM Program LEFT JOIN FY02_BASETABLE ON Program.Obligation =
FY02_BASETABLE.Program
WHERE (((FY02_BASETABLE.FY)=[forms]![password]![fy]))
ORDER BY FY02_BASETABLE.Program;

As you can probably tell, the preceding query retrieves all programs from
the program table and all programs from the fy02 basetable so as long as the
program names reside in both tables. However, I would like to change the
preceding query so that program names which are in the program table, but not
in the fy02 basetable are retrieved. What do I need to do to make this
happen? I tried to change the "=" to "<>" but to no avail.

Thanks in advance,
geebee