How To Resolve MS Access Join Expression Not Supported Issue

Is your Access Database frequently showing MS Access Join Expression Not Supported issue? Similarly like the issue mentioned in the below practical scenario. Then this blog will surely going to help you a lot in resolving this particular “Join Expression Not Supported” issue.  so just go through the post and get the best manual fixes to resolve it soon.

Practical Scenario:

I am writing a SQL query with inner join as this

select * from (table1 inner join table2 on table1.city = table2.code)   inner join table3 on table3.col1 = 5 and table3.col2 = ‘Hello’

This giving me the error “Join expression not supported”.

However, if I change the query like this then there is no error

select * from (table1 inner join table2 on table1.city = table2.code)   inner join table3 on table3.col1 = [SomeColumn] and table3.col2 = [SomeColumn]

Why is Access giving me an error on the first query?

What Is A Join In Access?

When you use multiple data source in Access Query, you use join to limit the records that you need to see, based on how the data source is related to each other. Join is also used to combine records from both data source, so that each of the record pair from source becomes records in query results.

By default join in access is automatically get created if there is already a relationship between two data sources that used in a query.

Joins works similarly like a query criteria in which it establish rules that the data must match to be included in the query operations. Unlike criteria, joins also signifies that each pair of the row which matches the join condition well get combined in the recordset to form a single row.

Basically there are 4 types of joins in Access: inner joins, outer joins, cross joins, and unequal joins.

Inner Joins Vs Outer Joins

Inner Joins:

In this join, only related data is combined from both tables. This means in this joining method Access only include data from table if there is corresponding data in the related table and vice versa. Mostly when you create a join without specifying the kind of joins then Access will consider it as inner join by default.

It join type is useful as they let you combine data from two sources based on shared values.  So you can only see data when there is a complete picture.

Outer Joins:

Whereas on the other hand outer join, contain all the related data combined correctly, along with the remaining records from one table. It is much similar like inner join, but adds the remaining rows from one of the tables.

Outer joins are directional too as it contain the option of left outer join and right outer join.

Left outer join include all the records from the left table i.e first table in the join whereas right outer join contains all the records from the right table which is 2nd table in the join.

You may also like:

5 Most Common “Access Query Errors” Along with Their Fixes

Access Query Designing: How To Run The Query Wizard In Access

How To Check The Joined Fields In Your Query?

Well if you want to check the data types of the field in a query then have a look at the source table in design view and inspect the properties of the field of which you are checking.

1.Firstly you need to open the query in design view. Joins indicated as line which connects field in the source tables. For each join note the table and its field names. 

2. From the Navigation Pane, make a right click on each table that has one more fields joined in the query and then click Design View.

Check the joined fields in your query 1

  • Number 1 circle indicates the joined fields with different data types.
  • Whereas, the number 2 circle takes you to the Design View which you get after right clicking over the Table option.

3. For each of the join, make a comparison for the values in the Data Type column of the table design grid for the fields already used in that join.

Check the joined fields in your query 2

  • Number 1 circle denotes the data type of the joined fields in table Design view.

4. So, to make a easy switch to a table to see its field, click the tab having that table’s name.

How To Fix MS Access Join Expression Not Supported error?

Below here you will get to know about the two main issues encountered in Access “Join Expression Not Supported” error along with its fixes. So, have a look over it:

Problem#1

This issue of Join Expression Not Supported is encountered when some crossed the limit of field length in the underlying SQL Server table linked into an Access application. User might have increased it to 255 characters in which Access considers it as a text and a memo field.

In particular cases, Access doesn’t allow either end of join in a query to be a memo field.

MS Access Join Expression Not Supported

The Solution

But the fixes to resolve this issue is quiet easier. The trick is to move the join into the WHERE clause of the query, like this:

SELECT Table1.*, Table2.*FROM Table1, Table2WHERE (Table1.MemoField=table2.MemoField);

Those who prefer it to the SQL View, here is the same query in the query builder:

fix join expression not supported error

Sometime Access will show some complaints while running out this query which is logically equivalent to the one that is abhored.

Problem#2: 

The “Join expression not supported query” error encounters at the time of opening a query in MS Access 2007. Meanwhile this, user can’t view the query nor make any editing to the query for correcting the alleged problem as the query view closes immediately. However you can execute the query from VBA and also export its results to Excel.

Solution:

Typing the following:

Debug.Print CurrentDb().QueryDefs(“YourQueryName”).SQL

Code in the VBA editor helps you in seeing the incorrect query definition. You can set the SQL property to new strings in order to override the query definition. However due to bug in MS Access in executing JOINS the query might denies to get opened despite of several attempts to repair.

Well the issue also arises when the query having the JOIN expression the links two tables using more than single column in each of them is been used. For example:

SELECT a.* FROM a LEFT JOIN b ON (a.col1=b.col1 AND b.col2=’value’)

As Access automatically and by default tends to strip the parenthesis from the join expression, that leads to error message mentioned earlier.

Conclusion:

The complete post is about Access join expression and its related issue. In this post we have discussed about the two main problematic situations under which user mostly gets such Access Join Expression Not Supported error. But not to worry as trying the below mentioned fixes will surely resolve your issue.  So, try them out and easy get rid of this Access Join Expression Not Supported error.

How To Resolve “MS Access Join Expression Not Supported” Issues In Access 2010/2013/2016