Syntax Error (Missing Operator) In Query Expression "

Question

Trying to write an SQL query where I'm using an inner join for getting data from one table and display matched contents in another table, however I receive the error 'Syntax error (missing operator) in query expression "'. Not sure where I'm going wrong with this, however I've inserted the code I'm using, and it looks correct to me.

By the way, the code has been written as part of a VB program, hence the extra code beside the SQL code.

Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [classes] INNER JOIN [students] ON classes.StudentForename = students.Forename AND ON classes.StudentSurname = students.Surname AND ON classes.TeacherName ='" & personloggedon.Text & "' AND ON classes.Day ='" & System.DateTime.Now.DayOfWeek.ToString & "' AND ON classes.Period ='" & attendance_reg_periodComboBox.Text & "'", con)

I've also attempted to use the following code also, again, not working.

Dim cmd as OleDbCommand = New OleDbCommand("SELECT [Forename], [Surname] FROM [classes] INNER JOIN [students] ON classes.StudentForename = students.Forename AND ON classes.StudentSurname = students.Surname AND ON classes.TeacherName ='" & personloggedon.Text & "' AND ON classes.Day ='" & System.DateTime.Now.DayOfWeek.ToString & "' AND ON classes.Period ='" & attendance_reg_periodComboBox.Text & "'", con)

All my SQL queries need to do is get the forenames and surnames off of the "classes" database as long as they match up to the criteria given, then take the forenames and surnames given from said database and match up to the "students" database. From there, it is then displayed in a datagridview on my program.

I'm relatively new to SQL coding, so if the error is particularly obvious, I apologise in advance! However, all help is greatly appreciated.

Table1 JOIN table2 on Table1.Field1 = Table2.Field1 AND Table1.Field1 = Table2.Field2 AND .... Remove the ON after the first one — Steve
– Steve, Commented Mar 23, 2018 at 20:44

B.Porter · Accepted Answer · 2018-03-23 20:58:46Z

4

You have too many ONs. Replace all AND ON with just AND.

Dim cmd as OleDbCommand = New 
OleDbCommand("SELECT [Forename], [Surname] FROM 
[classes] INNER JOIN [students] ON 
classes.StudentForename = students.Forename AND 
classes.StudentSurname = students.Surname WHERE 
classes.TeacherName ='" & personloggedon.Text & "' 
AND  classes.Day ='" & 
System.DateTime.Now.DayOfWeek.ToString & "' AND 
classes.Period ='" & 
attendance_reg_periodComboBox.Text & "'", con)

You really want to look at using parameterised queries though to avoid injection attacks.

edited Mar 23, 2018 at 20:58

answered Mar 23, 2018 at 20:43

B.Porter

3671 silver badge14 bronze badges

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

2 Comments

calvearn Over a year ago

Again, same here, I'm getting "Join expression not supported".

B.Porter Over a year ago

Sorry, didn't spot the missing WHERE condition. Try the edited version.

ashleedawg · Accepted Answer · 2018-03-23 21:08:17Z

ON is only used once per join.

Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [classes] INNER JOIN [students] ON classes.StudentForename = students.Forename AND classes.StudentSurname = students.Surname AND classes.TeacherName ='" & personloggedon.Text & "' AND classes.Day ='" & System.DateTime.Now.DayOfWeek.ToString & "' AND classes.Period ='" & attendance_reg_periodComboBox.Text & "'", con)

That's an awfully unusual (and messy) way to be calling the SQL though. It's tough to see if you have further issues. Let me know if it doesn't work and we will figure it out together.

Bonus Tip: Debugging VBA SQL Strings

When you get a VBA run-time error on a line that includes (or references) a complicated SQL string, it can be tricky to determine exactly where the problems lies.

As with troubleshooting any code, the process is to simplify, to eliminate possible culprits.

When attempting to execute the following procedure, we will get a run-time error on the line with the OpenRecordSet method:
Public Function SQLTest()
    Dim strSQL As String
    strSQL = "SELECT * FROM tblMain" & _
                "ORDER BY [TableID]"
    Debug.Print strSQL
    CurrentDb.OpenRecordset strSQL
End Function
To help identify the problem, we can modify the code slightly by adding a single line that will print the value of the SQL string to the Immediate Window.

(Use Ctrl+G to view the immediate window.)
Public Function SQLTest()
    Dim strSQL As String
    strSQL = "SELECT * FROM tblMain" & _
                "ORDER BY [TableID]"
    Debug.Print strSQL
    CurrentDb.OpenRecordset strSQL
End Function
Now, take a look at the output (in the immediate window):
SELECT * FROM tblMainORDER BY [TableID]
                    ↗↖
It's easy to see that we are missing a space between the table name and the ORDER BY clause, which is simple enough to fix:
strSQL = "SELECT * FROM tblMain " & _
     "ORDER BY [TableID]"
This is the possibly the single most helpful debugging technique for troubleshooting SQL statements in VBA.

See the Source for more debugging techniques.

More Information:

MSDN : Perform Joins Using Access SQL
TechNet : SQL Joins

EDIT:

Try this to troubleshoot:

Debug.Print "SELECT * FROM [classes] INNER JOIN [students] ON classes.StudentForename = students.Forename AND classes.StudentSurname = students.Surname AND classes.TeacherName ='" & personloggedon.Text & "' AND classes.Day ='" & System.DateTime.Now.DayOfWeek.ToString & "' AND classes.Period ='" & attendance_reg_periodComboBox.Text & "'"  
Stop

Put that code just before the problem line. When the code Stops hit Ctrl+G to view the immediate window.

Copy and Paste the SQL into a new Query Window (in SQL view) and try changing to Design Mode. See if you can identify the error there.

"Join expression not supported" I get when I try to use that command.
Managed to get an answer from previous solution, turns out I had mistaken my parameters for my sql code. However, thank you for your help 👍🏼

Collectives™ on Stack Overflow

Syntax Error (Missing Operator) In Query Expression "

2 Answers 2

2 Comments

Bonus Tip: Debugging VBA SQL Strings

More Information:

EDIT:

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Bonus Tip: Debugging VBA SQL Strings

More Information:

EDIT:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related