Saving this query field with no criteria

T

Tim

This simple query below with 'Select DISTINCT' shows me 1 contact,
regardless of how many times a contact matches to any of the multiple
fields and criteria in the WHERE. I can not show the fields from the
Left Joined tables or I'll get multiple contacts. That's ok though.

The problem I'm having is that I want to leave the criteria of the
fields in the Left Joined tables blank so that I can specify it as
part of a Filter String created in code attached to the On Open event
of a report.
If I leave the criteria blank, then these fields from the Left Joined
tables don't stay as part of my query fields because they aren't
shown, and have no criteria to make them useful. So when I run my
report, it fails because it doesn't find the fields in the query.

That's a lot harder to say than to see.
Anyone have a solution to this? Seems like it should be simple.

SELECT DISTINCT dbo_CustomerContact.Contact_Name
FROM ((dbo_CustomerContact LEFT JOIN dbo_ContactSoftware ON
dbo_CustomerContact.CustomerContact_ID =
dbo_ContactSoftware.CustomerContact_ID) LEFT JOIN
dbo_ContactSpecialList ON dbo_CustomerContact.CustomerContact_ID =
dbo_ContactSpecialList.CustomerContact_ID) LEFT JOIN
dbo_CustomerContactCategory ON dbo_CustomerContact.CustomerContact_ID
= dbo_CustomerContactCategory.CustomerContact_ID
WHERE (((dbo_CustomerContactCategory.Category_ID)=1 Or
(dbo_CustomerContactCategory.Category_ID)=2 Or
(dbo_CustomerContactCategory.Category_ID)=3) AND
((dbo_ContactSpecialList.List_ID)=1) AND
((dbo_ContactSoftware.Software_ID)=1 Or
(dbo_ContactSoftware.Software_ID)=2));
 
M

[MVP] S.Clark

When you add criteria to an Outer Join, sometimes it's like not having the
outer join at all.

You may need to break this one big query down to seperate queries and then
string them together. This will allow for the criteria to be applied at the
individual query level, without affecting the join.
 
T

Tim

Using two queries does at least save the fields in Query #1, because I
can show the fields in query #1, and then do a SELECT DISTINCT on
query #2 and not show them.
Problem still remains though that now I have to base my report on
Query #2, where the fields don't exist. I don't know how to
programmatically affect the criteria of query #1 when it is not used
as the reports control source.
Any thoughts on this?
 
Top