Access SQL JOIN Expression Not Supported

Question

I know there are a bunch of questions on here dealing of unsupported join expressions, I just can't find one that refers to my situation.

My query looks like this :

SELECT T1.*, T2.Year, T3.Force, T4.Group AS myGroup
FROM ((AllQuestions AS T1 
INNER JOIN Years AS T2 ON T1.ID_Year = T2.ID) 
INNER JOIN Forces AS T3 ON T1.Questions_Force = T3.ID)
INNER JOIN Groups AS T4 ON  T4.ID = " & (Me.Texte423) & "
ORDER BY Categories, T1.Questions;

As you can see on line refering to T4, I'm trying to match the ID in table Groups to match a value from a textbox in a report (it might be important to mention).

The error i'm getting looks like : JOIN expression not supported. "looks" because i'm using a french version of Access, so it's a basic translation...

What am I doing wrong please?

What error message are you getting? That always helps us.

nicomp
– nicomp

2015-11-27 13:30:11 +00:00
Commented Nov 27, 2015 at 13:30 — nicomp
– nicomp, Commented Nov 27, 2015 at 13:30
MS Access doesn't allow use constants in join conditions.

Arvo
– Arvo

2015-11-27 13:39:29 +00:00
Commented Nov 27, 2015 at 13:39 — Arvo
– Arvo, Commented Nov 27, 2015 at 13:39

Rob Paller · Accepted Answer · 2015-11-27 13:32:01Z

2

I think you will need to CROSS JOIN the table Groups and then place T4.ID in the WHEREclause of your query but this doesn't resolve the relationship that may exist between T4.ID and the other ID columns that are being used to join the other tables:

SELECT T1.*, T2.Year, T3.Force, T4.Group AS myGroup
FROM ((AllQuestions AS T1 
INNER JOIN Years AS T2 ON T1.ID_Year = T2.ID) 
INNER JOIN Forces AS T3 ON T1.Questions_Force = T3.ID)
CROSS JOIN Groups AS T4 
WHERE T4.ID = " & (Me.Texte423) & "
ORDER BY Categories, T1.Questions;

If there is a relationship between Groups and AllQuestions on ID then you need to modify your query along these lines:

SELECT T1.*, T2.Year, T3.Force, T4.Group AS myGroup
FROM ((AllQuestions AS T1 
INNER JOIN Years AS T2 ON T1.ID_Year = T2.ID) 
INNER JOIN Forces AS T3 ON T1.Questions_Force = T3.ID)
INNER JOIN Groups AS T4 ON T1.{Group ID Relation} = T4.ID
WHERE T4.ID = " & (Me.Texte423) & "
ORDER BY Categories, T1.Questions;

answered Nov 27, 2015 at 13:32

Rob Paller

7,77631 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gaétan Martin Over a year ago

Thx for your answer, I really never had to use cross joins but it could make sense... I tried the first solution since I don't want to use the relation between Groups and AllQuestions, but I get this error when executing : syntax error in the where clause, and the word CROSS is highlighted...

Andre Over a year ago

This is the solution, only Access doesn't have the CROSS JOIN keyword. Instead of it, simply use a comma , in the FROM list.

Gustav · Accepted Answer · 2015-11-27 13:35:51Z

0

You can't do that. But you can modify the SQL of the query. Set it to:

SELECT T1.*, T2.Year, T3.Force, T4.Group AS myGroup
FROM ((AllQuestions AS T1 
INNER JOIN Years AS T2 ON T1.ID_Year = T2.ID) 
INNER JOIN Forces AS T3 ON T1.Questions_Force = T3.ID)
INNER JOIN Groups AS T4 ON  T4.ID = {0}
ORDER BY Categories, T1.Questions;

Then set/modify the SQL when you need it:

MyQdy.SQL = Replace(SQL, "{0}", Me!Texte423.Value)

answered Nov 27, 2015 at 13:35

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

2 Comments

Gaétan Martin Over a year ago

I tried that and I get an error saying : The format of this GUID is incorrect: "T4.ID = {0 ".

Gustav Over a year ago

You need, of course, to run the replace before running the SQL.

Collectives™ on Stack Overflow

Access SQL JOIN Expression Not Supported

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related