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.
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.