Microsoft Access can’t represent the join expression

[gasmaskman]

I have the following query and when I attempt to view it in Design Mode I get the error: "Microsoft Access can’t represent the join expression". I kow that parentheses can cause this issue, but I don't see any extra parentheses included in this query. Any help is greatly appreciated.

SELECT * FROM [Summary File] INNER JOIN [Number Reference] 
ON ([Summary File].[SALE NAME] = [Number Reference].[Number ID]) OR ([Summary File].[SALE NAME] = [Number Reference].[Alternate ID])
WHERE ([Number Reference].[Service Number]=[forms]![Maverick Data].[svcnumber1]);

[orange]

Have you checked the format and syntax of an Inner Join?
Here's a link

Welcome to the forum and good luck with your project.

[ItsMe]

Looks like two different data types
SALE NAME] = [Number Reference].[Number ID])

[gasmaskman]

ItsMe, the data types for all fields are listed as text.

orange, I reviewed the format and syntax and could not find anything in the code that stands out as a problem, expect for adding a line break before 'INNER JOIN'.

[ItsMe]

Some statements can not be represented in design view. The OR operator is what is causing the issue for you here.

[gasmaskman]

Hmmm, I was really hoping that the would be some type of work around. Thanks for the help!

[orange]

gasmaskman,


I'm going to change my response, for the moment. I did see a post where the poster was trying to set up sql whereby a "Property (land/house)" could involve 2 agents. It was not in his original specs where he had allowed for 1 agent to be associated with a Property. As a quick fix he adjusted his Property table to include a second Agent field (Agent2). He tried the query wizard to set up the relationship and could not get it to work.

His solution, and he said it works and in his case it's quite fast, was to include an OR condition on a left Join. I have not tested it, but I have never seen an OR condition in a JOIN before. So, I remain a little skeptical at the moment - I want to see it work. I do have a lot of respect for the OP and the posters who responded.
Here is the link.