Query runs fine but I get an error applying a report filter

[btrini10]

Hello All, I have a report which is based on a query.The query runs fine on its own. I have a form which builds a filter for the report. When I attempt to apply the filter to the report I get the message "The specified field '[REVIEW_TABLE]![REVIEW_ID]' could refer to more than one table listed in the FROM clause of your SQL statement".

Below is the SQL for the query followed by the filter that is beign built.

SELECT REVIEW_TABLE.[REVIEW ID], REVIEW_TABLE.REVIEW_FILE_ID, REVIEW_TABLE.REVIEW_UNIT, REVIEW_TABLE.REVIEW_NAME, REVIEW_TABLE.REVIEW_STATUS, REVIEW_TABLE.REVIEW_DESCRIPTION, REVIEW_TABLE.REVIEW_CONCLUSION, STATUS_TABLE.[STATUS NAME], REVIEW_TABLE.REVIEW_START_DATE, REVIEW_TABLE.REVIEW_END_DATE, REVIEW_TABLE.REVIEW_DRAFT_ALERT_DATE, REVIEW_TABLE.REVIEW_MGT_MEET_DATE, "VIEW" AS [VIEW], REVIEW_TABLE.REVIEW_FLDWK_END_DATE, IIf(CONTACT_TABLE!CONTACT_LNAME<>'',CONTACT_TABLE!CONTACT_LNAME & ', ' & CONTACT_TABLE!CONTACT_FNAME & ' ' & CONTACT_TABLE!CONTACT_MNAME,CONTACT_TABLE!CONTACT_FNAME) AS [EXEC], UNIT_TABLE.[UNIT ID], UNIT_TABLE.[UNIT NAME], tbl_CONTROL_ISSUE.ISSUE_ID, tbl_CONTROL_ISSUE.CONTROL_ISSUE, CASE_TABLE.CASE_FILE_ID, CASE_TABLE.CASE_REAL_ID, CASE_TABLE.CASE_NAME, NON_CASE_TABLE.NON_CASE_ID, NON_CASE_TABLE.PRTCPNT_FNAME, NON_CASE_TABLE.PRTCPNT_LNAME, NON_CASE_TABLE.PRTCPNT_PIN, NON_CASE_TABLE.PRTCPNT_MNAME, CONTACT_TABLE.CONTACT_ID, RE_RECOVERIES.RE_OBS_NOTE_HDR, REVIEW_TABLE.REVIEW_PRIORITY, qry_CASE_PROPERTY_UNIQUE.CountOfPROPERTY_ID, 1 AS [COUNTER], qry_ALL_AMOUNTS_crosstab_Case.LOSS, qry_ALL_AMOUNTS_crosstab_Case.RECOV, NZ(qry_ALL_AMOUNTS_crosstab_Case!LOSS)+Nz(qry_ALL_AMOUNTS_crosstab_Case!RECOV)+Nz(qry_ALL_AMOUNTS_crosstab_Case!REFF)+Nz(qry_ALL_AMOUNTS_crosstab_Case!WRT_OFF) AS PENDING, qry_REVIEW_OPS_CASE_COUNT_GRP.SumOfOPS_CASE_COUNT, qry_COUNT_REFERRED_CASES_SINGLE_REVIEW.CountOfCASE_FILE_ID, qry_FOR_TRAINING_GRP.SumOfTRAINING_COUNT
FROM tbl_CONTROL_ISSUE RIGHT JOIN (((((((((((CONTACT_TABLE RIGHT JOIN REVIEW_TABLE ON CONTACT_TABLE.CONTACT_ID = REVIEW_TABLE.REVIEW_EXEC) LEFT JOIN STATUS_TABLE ON REVIEW_TABLE.REVIEW_STATUS = STATUS_TABLE.[STATUS ID]) LEFT JOIN CASE_TABLE ON REVIEW_TABLE.[REVIEW ID] = CASE_TABLE.REVIEW_ID) LEFT JOIN RE_RECOVERIES ON REVIEW_TABLE.REVIEW_FILE_ID = RE_RECOVERIES.CASE_FILE_ID) LEFT JOIN UNIT_TABLE ON REVIEW_TABLE.REVIEW_UNIT = UNIT_TABLE.[UNIT ID]) LEFT JOIN qry_CASE_PROPERTY_UNIQUE ON REVIEW_TABLE.REVIEW_FILE_ID = qry_CASE_PROPERTY_UNIQUE.CASE_FILE_ID) LEFT JOIN qry_ALL_AMOUNTS_crosstab_Case ON REVIEW_TABLE.REVIEW_FILE_ID = qry_ALL_AMOUNTS_crosstab_Case.REVIEW_FILE_ID) LEFT JOIN qry_REVIEW_OPS_CASE_COUNT_GRP ON REVIEW_TABLE.[REVIEW ID] = qry_REVIEW_OPS_CASE_COUNT_GRP.[REVIEW ID]) LEFT JOIN qry_COUNT_REFERRED_CASES_SINGLE_REVIEW ON REVIEW_TABLE.[REVIEW ID] = qry_COUNT_REFERRED_CASES_SINGLE_REVIEW.REVIEW_ID) LEFT JOIN qry_FOR_TRAINING_GRP ON REVIEW_TABLE.[REVIEW ID] = qry_FOR_TRAINING_GRP.REVIEW_ID) LEFT JOIN NON_CASE_TABLE ON REVIEW_TABLE.[REVIEW ID] = NON_CASE_TABLE.REVIEW_ID) ON tbl_CONTROL_ISSUE.ISSUE_ID = REVIEW_TABLE.REVIEW_CATEGORY
WHERE (((REVIEW_TABLE.REVIEW_FILE_ID)<>"201100R") AND ((REVIEW_TABLE.REVIEW_STATUS)=1 Or (REVIEW_TABLE.REVIEW_STATUS)=2) AND ((tbl_CONTROL_ISSUE.ISSUE_ID)<>22));



([REVIEW_TABLE]![REVIEW ID] Like '*' OR [REVIEW_TABLE]![REVIEW ID] Is Null ) AND ([CASE_TABLE]![CASE_FILE_ID] Like '*' OR [CASE_TABLE]![CASE_FILE_ID] Is Null) AND ([NON_CASE_TABLE]![NON_CASE_ID] Like '*' OR [NON_CASE_TABLE]![NON_CASE_ID] Is Null ) AND ([REVIEW_TABLE]![REVIEW_STATUS] = 1) AND ([NON_CASE_TABLE].[PRTCPNT_PIN] Like '*' OR [NON_CASE_TABLE].[PRTCPNT_PIN] Is Null ) AND ([CASE_TABLE]![CASE_NAME] Like '*' OR [CASE_TABLE]![CASE_NAME] Is Null) AND ([REVIEW_TABLE]![REVIEW_NAME] Like '*' OR [REVIEW_TABLE]![REVIEW_NAME] Is Null) AND (([NON_CASE_TABLE]![PRTCPNT_FNAME] Like '*' OR [NON_CASE_TABLE]![PRTCPNT_FNAME] Is Null) OR ([NON_CASE_TABLE]![PRTCPNT_LNAME] Like '*' OR [NON_CASE_TABLE]![PRTCPNT_LNAME] Is Null) OR ([NON_CASE_TABLE]![PRTCPNT_MNAME] Like '*' OR [NON_CASE_TABLE]![PRTCPNT_MNAME] Is Null)) AND ([CONTACT_TABLE]![CONTACT_ID] Like '*' OR [CONTACT_TABLE]![CONTACT_ID] IS Null) AND (([REVIEW_TABLE]![REVIEW_START_DATE] >= #1/1/1900# AND [REVIEW_TABLE]![REVIEW_START_DATE] <= #12/12/9999#) OR [REVIEW_TAB
LE]![REVIEW_START_DATE] Is Null) AND (([REVIEW_TABLE]![REVIEW_FLDWK_END_DATE] >= #1/1/1900# AND [REVIEW_TABLE]![REVIEW_FLDWK_END_DATE] <= #12/12/9999#) OR [REVIEW_TABLE]![REVIEW_FLDWK_END_DATE] Is Null) AND (([REVIEW_TABLE]![REVIEW_DRAFT_ALERT_DATE] >= #1/1/1900# AND [REVIEW_TABLE]![REVIEW_DRAFT_ALERT_DATE] <= #12/12/9999#) OR [REVIEW_TABLE]![REVIEW_DRAFT_ALERT_DATE] Is Null) AND (([REVIEW_TABLE]![REVIEW_END_DATE] >= #1/1/1900# AND [REVIEW_TABLE]![REVIEW_END_DATE] <= #12/12/9999#) OR [REVIEW_TABLE]![REVIEW_END_DATE] Is Null) AND (([REVIEW_TABLE]![REVIEW_MGT_MEET_DATE] >= #1/1/1900# AND [REVIEW_TABLE]![REVIEW_MGT_MEET_DATE] <= #12/12/9999#) OR [REVIEW_TABLE]![REVIEW_MGT_MEET_DATE] Is Null) AND ([RE_RECOVERIES]![RE_OBS_NOTE_HDR] Like '*' OR [RE_RECOVERIES]![RE_OBS_NOTE_HDR] Is Null ) AND ([UNIT_TABLE]![UNIT ID] Like '*' OR [UNIT_TABLE]![UNIT ID] IS Null)

Any help would be appreciated.

 

[dhookom]

How are you attempting to apply a filter? I typically use the WHERE CONDITION of the DoCmd.OpenReport method. I never have two fields in a record source that have the same name and I never reference a table name in my WHERE CONDITION clause.

Duane
Hook'D on Access
MS Access MVP

 

[btrini10]

Hey Duane

Below is the code I use to apply the where condition and open my report.
DoCmd.OpenReport StDocName, acPreview, WhereCondition:=strFilter

How would you recommend I build the filter without a table reference?

Thanks

 

[dhookom]

I never use the table names in my Where Conditions and don't remember ever getting that error message.

Duane
Hook'D on Access
MS Access MVP

 

[btrini10]

Thanks Duane, that did the trick.