1

I'm getting this particular error for a couple hours now and I still can't figure out for the life of me what's wrong with my select statement, I'm trying to connect 3 tables.

"SELECT POItem.PO AS 'Purchase_Order', POItem.Qty AS 'Quantity', 
POItem.BCurr as 'Currency', POItem.TotalCost, PO.Vendor, Master.Desc1 
FROM PO 
LEFT JOIN POItem ON PO.ID = POItem.PO AND 
INNER JOIN Master ON Master.IPN = POItem.IPN 
WHERE POItem.IPN = '" & TextBox1.Text & "' 
ORDER BY POItem.PO DESC"
Improve this question
5
  • 1
    there is no need of AND before an inner join. Commented Jun 15, 2015 at 8:29
  • If you are not familiar with MS Access SQL, it is best to use the query design window. Commented Jun 15, 2015 at 8:37
  • @akhilkumar Unfortunately there seems to be another error once i remove the AND. It says Syntax error (missing operator) in query expression 'PO.ID = POItem.PO INNER JOIN Master ON Master.IPN = POItem.IPN'. Commented Jun 15, 2015 at 8:38
  • @Fionnuala Will do! Will definitely try out the query design window! Commented Jun 15, 2015 at 8:46
  • @akhilkumar I've solved it now! Thanks for the effort! Much appreciated! Commented Jun 15, 2015 at 8:47

1 Answer 1

Reset to default
3

There is a AND keyword that is not necessary at this position (just in front of INNER JOIN). If you remove it and add brackets around the LEFT JOIN, your query should work:

"SELECT POItem.PO AS 'Purchase_Order', POItem.Qty AS 'Quantity', 
POItem.BCurr as 'Currency', POItem.TotalCost, PO.Vendor, Master.Desc1 
FROM (PO 
LEFT JOIN POItem ON PO.ID = POItem.PO) 
INNER JOIN Master ON Master.IPN = POItem.IPN 
WHERE POItem.IPN = '" & TextBox1.Text & "' 
ORDER BY POItem.PO DESC"

By the way, instead of including the value of TextBox1.Text in the string, rather use Parameters in order to avoid SQL injection attacks.

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

7 Comments

MS Access is very fussy indeed about brackets on joins. This will not work.
Unfortunately there seems to be another error once i remove the AND. It says Syntax error (missing operator) in query expression 'PO.ID = POItem.PO INNER JOIN Master ON Master.IPN = POItem.IPN'.
@Fionnuala is right, MS Access needs some more brackets. I update my post.
@Ange1 Left outer join is not used in MS Access.
Hi Melissa, StackOverflow allows you to accept answers that worked for you but it is not required. If you want to accept an answer, just click the little tick mark.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.