Syntax error in Join operation

[brucerwalker]

I understand that . I was following up on Jdraw's last question. Then what would you change in the code to eliminate the join and have the code run as it should? All the other 5 selection parameters are chosen through drop downs and all work as they are supposed to, but I am not sure why the code is different when choosing the part number drop down. Thanks.

 

[jdraw]

Bruce,

The point Paul is making is that you can not have this construct

.....PROGRAMS INNER JOIN TEST ON ( Request.RequestID = Test.RequestID AND Test.PartNumber = '236889-01')....

You should move the

Test.PartNumber = '236889-01'

into the WHERE clause.

Please post the enter code for the sub involved, and I'll look at it.

 

[brucerwalker]

So should the cod look like this;

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

 

[jdraw]

No.
The code you just showed should look like this (untested)

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

but we haven't seen all code for the Sub.

 

[pbaldy]

No.
The code you just showed should look like this (untested)

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

but we haven't seen all code for the Sub.

An oops:

query = query & " WHERE Test.PartNumber = '" & Me.ComboSearchPart & "' [COLOR="Red"]"[/COLOR] and Request.CustomerID = Customer.CustomerID and Request.ProgramID = Programs.ProgramID"

 

[brucerwalker]

The complete sub is on the first page of this post. I will check against what you just sent. Thanks for your help!

 

[jdraw]

Thanks Paul for highlighting the double quote that is in error. It should not be there.

Bruce,

A Sub......... ends with End Sub --- which I don't see on the first page.

Did you try the revised code --don't forget to remove the double quote highlighted in red in Paul's post.

Good luck.

 

[pbaldy]

No problem. I've very good at finding other people's goofs...my own, not so much.

 

[jdraw]

I think, by my recent example, I am in the same camp.
Thanks for another pair of eyes.

 

[Cronk]

As an aside, I would not be using a string variable name ('query') which is a form propety/method name in latter versions of Access. Use strQuery or strSQL instead.

 

[brucerwalker]

Yes end sub is at the end of the code. I must have missed it when I copied and pasted the code in here. Sorry. So I changed the code as was suggested. Code is below.

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

When I run it I get the run time error 3135 syntax error in join operation.

 

[plog]

Are the joined fields the same data types?

Is one of Request.RequestID or Test.RequestID numeric and one text?

Honestly, you need to put down the VBA, open up a query object and get this SQL to run as you want. Then once you have a working query, move it back into VBA and dissect it as needed.

 

[jdraw]

Bruce,
This is a best guess at the moment

...ber = [COLOR="Red"]' "[/COLOR] & Me.ComboSearchPart & [COLOR="Red"]" '[/COLOR]

Remove the space between ' " and " '

Try '" before & Me.ComboSearchPart & and "' and Request.CustomerID.....

Also keep a debug.print query in the code so we can see the rendered version of query.

 

[brucerwalker]

Paul,
Both data types are number. As I said previously I am a beginner at this, so I am not sure what you mean in your second statement by putting down the VBA ad opening a query object. Are you saying I should create a query that pulls the data by part number and then convert into VBA? I could likely do that with some direction.

 

[brucerwalker]

When I run debug.print query, this is what is in the immediate window;

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

 

[jdraw]

If you copy that info; then go to Create Query, then SQL view; then paste the info into the sql window, then run the query

What happens???

 

[brucerwalker]

When I run the query it says Syntax error in JOIN operation.

 

[jdraw]

Is it possible to make a copy of the database, or create some subset that can be tested?

 

[brucerwalker]

It Is possible. How would I get it to you?

 

[jdraw]

I hope you are working on a copy of your database, and NOT the production/operational version.
Is this a split database with Frontend and backend? Or is it a single accdb or mdb file?

You can do a compact and repair of the current database C.
Create a new empty database N,
then import all objects from your compacted database C
into the new database N,
make a zip file of the new database N

attach it to a post.