Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

btrini10

IS-IT--Management
Dec 5, 2006
73
US
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.


 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top