strWhere criteria

Let's see how this goes over...I don't think it can be done.
I am filtering a report with multiple filters (6). The switch and report
runs fine using a strWhere variable that combines the strWhere clauses that
the user selects, like
strWhere = strWhere & " AND Priority = '" & Me.CmboPri & "'"

My challenge is to add criteria from a joined table that contains comments,
with the print filter (combo row source) being '1 -Show only Standards with
Comments;2 -Show only Standards without Comments'

this is the SQL from what I want to show for the ones without comments:
SELECT tblStandards.STD_No
FROM tblStandards LEFT JOIN tblXComments ON tblStandards.STD_No =
tblXComments.StdNo
WHERE (((tblXComments.Cmt_ID) Is Null));

so, I need a where clause to add to the list of strWhere that will allow me
to filter the comments. The report source query is complex and I can't add
in the joined comments table to it without screwing up the report data.

Maarkr wrote:

Let's see how this goes over...I don't think it can be done.
I am filtering a report with multiple filters (6). The switch and report
runs fine using a strWhere variable that combines the strWhere clauses that
the user selects, like
strWhere = strWhere & " AND Priority = '" & Me.CmboPri & "'"

My challenge is to add criteria from a joined table that contains comments,
with the print filter (combo row source) being '1 -Show only Standards with
Comments;2 -Show only Standards without Comments'

this is the SQL from what I want to show for the ones without comments:
SELECT tblStandards.STD_No
FROM tblStandards LEFT JOIN tblXComments ON tblStandards.STD_No =
tblXComments.StdNo
WHERE (((tblXComments.Cmt_ID) Is Null));

so, I need a where clause to add to the list of strWhere that will allow me
to filter the comments. The report source query is complex and I can't add
in the joined comments table to it without screwing up the report data.


Presumably, the report's record source query includes the
STD_No field. If so, then you can change the report's
recource to your query above, but changing it to use your
complicated query unstead of tblStandards.

--
Marsh
MVP [MS Access]

If the field(s) you want to use to filter your report aren't in the report's
record source query then you can't include them in the strWhere as used in
DoCmd.OpenReport.

I would either hard-code form/control references in a saved query or
dynamically build the SQL of a saved query that can be used for filtering.

--
Duane Hookom
Microsoft Access MVP


"Maarkr" wrote:

Let's see how this goes over...I don't think it can be done.
I am filtering a report with multiple filters (6). The switch and report
runs fine using a strWhere variable that combines the strWhere clauses that
the user selects, like
strWhere = strWhere & " AND Priority = '" & Me.CmboPri & "'"

My challenge is to add criteria from a joined table that contains comments,
with the print filter (combo row source) being '1 -Show only Standards with
Comments;2 -Show only Standards without Comments'

this is the SQL from what I want to show for the ones without comments:
SELECT tblStandards.STD_No
FROM tblStandards LEFT JOIN tblXComments ON tblStandards.STD_No =
tblXComments.StdNo
WHERE (((tblXComments.Cmt_ID) Is Null));

so, I need a where clause to add to the list of strWhere that will allow me
to filter the comments. The report source query is complex and I can't add
in the joined comments table to it without screwing up the report data.