ACC2000: "Microsoft Access can't represent the join expression" Error Message in Query DesignID: Q207868
|
After you create a query with the Simple Query Wizard in Microsoft Access, you may receive the following error message if you try to open the query in Design view:
Microsoft Access can't represent the join expression
([<TableName>].[<FieldName>] =
[<TableName>].[<FieldName>]) AND
([<TableName>].[<FieldName>] =
[<TableName>].[<FieldName>]) in Design
view.
The Simple Query Wizard adds an extra set of parentheses in the join
expression of the SQL statement that it creates for the query.
The error occurs if your query meets both of the following conditions:
You can use either of the following methods to work around this problem:
ON ([tblOne].[Key3] = [tblTwo].[Key3]) AND (([tblOne].[Key1]
= [tblTwo].[Key1]) AND ([tblOne].[Key2] = [tblTwo].[Key2]));
This is how the expression looks with the extra parentheses removed:
ON ([tblOne].[Key3] = [tblTwo].[Key3]) AND ([tblOne].[Key1]
= [tblTwo].[Key1]) AND ([tblOne].[Key2] = [tblTwo].[Key2]);
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.
Table: tblOne
--------------------
Field Name: Key1
Data Type: Text
Field Name: Key2
Data Type: Text
Field Name: Key3
Data Type: Text
Field Name: Field1
Data Type: Number
Table: tblTwo
------------------------
Field Name: ID
Data Type: AutoNumber
Field Name: Key1
Data Type: Text
Field Name: Key2
Data Type: Text
Field Name: Key3
Data Type: Text
Field Name: Field2
Data Type Text
Table/Query: tblOne Related Table/Query: tblTwo
-------------------------------------------------
Key1 Key1
Key2 Key2
Key3 Key3
For more information about creating relationships between tables, click Microsoft Access Help on the
Help menu, type "create or modify relationships" in the Office Assistant or the Answer Wizard,
and then click Search to view the topics returned.
Additional query words: pra
Keywords : kberrmsg kbdta QryOthr
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug
Last Reviewed: May 17, 1999