Syntax error in Join operation

[brucerwalker]

I have a form with a sub form in it. I have various ways to search for data with drop down boxes to pick from a list and then hit a button to search. I have one that is not working properly. It is a drop where one can pick a part number and then when you search it should bring up all records related to that part number. However I get run time error 3135 and it says syntax error in Join operation. The code it is running I think is;

'Field Parts
If (Not IsNull(Me.ComboSearchPart)) Then
query = query & " INNER JOIN TEST ON ( Request.RequestID = Test.RequestID AND Test.PartNumber = '" & Me.ComboSearchPart & "')"
End If

Any help solving this problem would be greatly appreciated.

 

[plog]

I've seen a lot of weird SQL things that don't seem right but work, so I am reluctant to say that code won't work (even if you fix that syntax). But it is wrong:

Test.PartNumber = '" & Me.ComboSearchPart & "')"

That code should be in the WHERE clause not a JOIN. You are testing Test.PartNumber for a value with it, not using it to JOIN Test to Request.

To truly find out the issue, find out what your query holds. After you are done building it, spit it out to see exactly what's in it.

 

[pbaldy]

You can see if this helps:

http://www.baldyweb.com/ImmediateWindow.htm

but like plog my gut is that join syntax won't work in Access (it will in SQL Server).

 

[brucerwalker]

We had someone make this database for us. Is it possible to create a query that puts the fields I need into a form and then apply a filter to a part number field to see only the records for that part number. This is a database we store test information on products and one part can have multiple tests done it. I want the filter to show all the tests for that part. Thanks.

 

[pbaldy]

Certainly. As plog said that would normally be a criteria, not a join. Or:

http://www.baldyweb.com/wherecondition.htm

 

[brucerwalker]

When I run Debug the following line is highlighted;

Me.SubFormSearch.Form.RecordSource = query

When I hold the cursor over this line it references;

query = "SELECT Request.*, Customer.Name, Programs.ProgramName FROM REQUEST, CUSTOMER, PROGRAMS "

The complete code is below. Any further advice?? Thanks.

Private Sub Command68_Click()
Dim query As String
Dim MTO As Boolean
MTO = False

query = "SELECT Request.*, Customer.Name, Programs.ProgramName FROM REQUEST, CUSTOMER, PROGRAMS "

'Field Parts
If (Not IsNull(Me.ComboSearchPart)) Then
query = query & " INNER JOIN TEST ON ( Request.RequestID = Test.RequestID AND Test.PartNumber = '" & Me.ComboSearchPart & "')"
End If
query = query & "WHERE Request.CustomerID = Customer.CustomerID and Request.ProgramID = Programs.ProgramID"
MTO = True

'Fiel ID
If (Not IsNull(Me.ComboSearchById)) Then

If (MTO) Then
query = query & " And "
Else
MTO = True
End If
query = query & " Request.RequestID =" & Me.ComboSearchById
End If
'Field Customer
If (Not IsNull(Me.ComboCustomer)) Then
If (MTO) Then
query = query & " And "
Else
MTO = True
End If

Dim CId As Integer
CId = DLookup("CustomerID", "Customer", "Customer.Name = '" & Me.ComboCustomer & "'")
query = query & " Customer.CustomerID =" & CId
End If
'Field Requested By
If (Not IsNull(Me.ComboRequestedBy)) Then
If (MTO) Then
query = query & " And "
Else
MTO = True
End If
query = query & " RequestorID =" & Me.ComboRequestedBy
End If
'Field Inital Date
If (Not IsNull(Me.TextInitialDate)) Then
If (MTO) Then
query = query & " And "
Else
MTO = True
End If
query = query & " RequestDate >= #" & Me.TextInitialDate & "#"
End If
'Field Final Date
If (Not IsNull(Me.TextFinalDate)) Then
If (MTO) Then
query = query & " And "
Else
MTO = True
End If
query = query & " FinishDate <= #" & Me.TextFinalDate & "#"
End If

'Field Stardard
If (Not IsNull(Me.ComboSearchByStandard)) Then
If (MTO) Then
query = query & " And "
Else
MTO = True
End If
query = query & " Request.StandardID = '" & Me.ComboSearchByStandard & "'"

End If


query = query & ";"
'MsgBox query
Me.SubFormSearch.Form.RecordSource = query

 

[plog]

Again, find out what query contains. Not in general ("it contains my query"), in specific (the characters it has in it). And then make sure that it is valid SQL by pasting it into a query and seeing if it runs.


Check out the first link pbadly put up.

 

[brucerwalker]

Just so I am clear are you referring to this statement;

query = "SELECT Request.*, Customer.Name, Programs.ProgramName FROM REQUEST, CUSTOMER, PROGRAMS "

which I understand as all fields from the request table, customer name from the customer table and program name from the programs table?

Sorry if I am slow but I am a beginner at this. Thanks.

 

[jdraw]

Take the single quote from this line
'MsgBox query
and also add a line
Debug.print query right after the msgbox line

put a single quote at the beginning of this to make it a comment for the moment.
'Me.SubFormSearch.Form.RecordSource = query

Copy what gets printed in the immediate window and post it.
Good luck

 

[brucerwalker]

So when I did that and ran the code by choosing a part number I got a MsgBox that had;

"SELECT Request.*, Customer.Name, Programs.ProgramName FROM REQUEST, CUSTOMER, PROGRAMS INNER JOIN TEST ON ( Request.RequestID = Test.RequestID AND Test.PartNumber = 236894-01
WHERE Request.CustomerID = Customer.CustomerID and Request.ProgramID = Programs.ProgramID;

 

[plog]

Count your left and right parenthesis.

 

[jdraw]

1 -Was there a trailing/terminating double quote?
2- How is PartNumber defined in your TEST table?
If it is a string then it needs to be in quotes.

We'll get to that next.
Good point plog.

Note: from the code you supplied earlier, it seems there should be single quotes around the partnumber value

If (Not IsNull(Me.ComboSearchPart)) Then
query = query & " INNER JOIN TEST ON ( Request.RequestID = Test.RequestID AND Test.PartNumber = '" & Me.ComboSearchPart & "')"

 

[brucerwalker]

Sorry in copying and pasting the different lines into my replay I missed the right parentheses. It is there.

The part number is chosen from a query through a drop down box and is a text field in the table.

 

[jdraw]

Please post the whole thing. Run it with the debug and copy what's printed in the immediate window.

 

[brucerwalker]

Here is what was in the immediate window;

SELECT Request.*, Customer.Name, Programs.ProgramName FROM REQUEST, CUSTOMER, PROGRAMS INNER JOIN TEST ON ( Request.RequestID = Test.RequestID AND Test.PartNumber = '40243.44AF36BD1')WHERE Request.CustomerID = Customer.CustomerID and Request.ProgramID = Programs.ProgramID;

 

[pbaldy]

As was mentioned a month ago, that join syntax (the fixed value) is not supported in Access. It should be in the WHERE clause (and frankly what you have in the WHERE clause now should be done with joins). You're also missing a space before WHERE.

 

[jdraw]

Bruce,

As Paul says, put a space before the W in the WHERE clause

query = query & "WHERE Request.CustomerID = Customer.CustomerID and Request.ProgramID = Programs.ProgramID"

to make it

query = query & [COLOR="Red"]" W[/COLOR]HERE Request.CustomerID = Customer.CustomerID and Request.ProgramID = Programs.ProgramID"

Also, I'm not sure which fixed part Paul is referring to. This SQL works perfectly fine in Access.

SELECT BOOKAuthors.BookID
, AUTHOR.authorId, AUTHOR.Firstname, AUTHOR.Lastname
, BOOK.BookTitle, BOOK.NumPages
FROM BOOK , AUTHOR, BOOKAuthors
 WHERE 
AUTHOR.authorId = BOOKAuthors.AuthorId AND
BOOK.BookId = BOOKAuthors.BookID
ORDER BY 
BOOK.BookTitle, AUTHOR.Lastname;

What is the latest status with your project?

 

[pbaldy]

Also, I'm not sure which fixed part Paul is referring to.

The fixed value in the join, like:

INNER JOIN...ON Field name = 'abc'

 

[brucerwalker]

Okay I added the space between " Where and ran it again. Below is what was in the immediate window;
SELECT Request.*, Customer.Name, Programs.ProgramName FROM REQUEST, CUSTOMER, PROGRAMS INNER JOIN TEST ON ( Request.RequestID = Test.RequestID AND Test.PartNumber = '236889-01') WHERE Request.CustomerID = Customer.CustomerID and Request.ProgramID = Programs.ProgramID;

 

[pbaldy]

I don't know how to say this any more clearly. You can NOT have a join like:

AND Test.PartNumber = '236889-01'

in Access.