SQL Inner Join Problem

I have added a third table to a SQL statement in VBA. I now get an error saying there is a missing operator in the query expression. I created one just like it using query builder and can't see a difference. The new addition is marked now with a # at the beginning of the line.

sql = "SELECT "
sql = sql + " dbo_OF_TransmissionHdr.isGift,"
sql = sql + " dbo_OF_TransmissionHdr.MacolaOrderNo, "
sql = sql + " dbo_OF_TransmissionHdr.SendEmailFlag, "
sql = sql + " dbo_OF_TransmissionHdr.transmissionControlNumber, "
sql = sql + " dbo_OF_TransmissionHdr.messageControlNumber, "
sql = sql + " dbo_OF_TransmissionHdr.PurchaseOrderNumber, "
sql = sql + " dbo_OF_TransmissionHdr.ShipMethod, "
sql = sql + " dbo_OF_LineItemDetail.ItemId, "
sql = sql + " dbo_OF_LineItemDetail.productName, "
sql = sql + " dbo_OF_LineItemDetail.QuantityToShip, "
sql = sql + " tblDotComCartonMaster.TrackingID"
sql = sql + " FROM dbo_OF_TransmissionHdr "
sql = sql + " INNER JOIN dbo_OF_LineItemDetail ON"
sql = sql + " (dbo_OF_TransmissionHdr.messageControlNumber = "
sql = sql + " dbo_OF_LineItemDetail.messageControlNumber) "
sql = sql + " AND (dbo_OF_TransmissionHdr.transmissionControlNumber ="
sql = sql + " dbo_OF_LineItemDetail.transmissionControlNumber)"
#sql = sql + " INNER JOIN tblDotComCartonMaster ON"
#sql = sql + " (dbo_OF_TransmissionHdr.messageControlNumber = "
#sql = sql + " tblDotComCartonMaster.messageControlNumber) "
#sql = sql + " AND (dbo_OF_TransmissionHdr.transmissionControlNumber ="
#sql = sql + " tblDotComCartonMaster.transmissionControlNumber)"
sql = sql + " WHERE (((dbo_OF_TransmissionHdr.SendEmailFlag)=False) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.ResponseCode)='00') "
sql = sql + " AND ((dbo_OF_TransmissionHdr.killFlag)=False) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.DupePOFlag)=False) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.PickListPrinted)=True) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.OrderCompleteFlag)=True) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.PackListPrinted)=True))"
sql = sql + " ORDER BY dbo_OF_TransmissionHdr.MacolaOrderNo; "
chantalcookwareAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kirenievsCommented:
Try to do a debug.print sql. Ctl+G displays the direct window. Copy the text into a query and see if you could find the error there.

Another comment: You will get less typing using this way:

sql = "SELECT " _
  & " dbo_OF_TransmissionHdr.isGift," _
  & " dbo_OF_TransmissionHdr.MacolaOrderNo, "

will_scarlet7Commented:
I played a bit with the join. See if it works better:

sql = sql + " FROM (dbo_OF_TransmissionHdr "                                                '<Starting parenthesis for first join
sql = sql + " INNER JOIN dbo_OF_LineItemDetail "
sql = sql + " INNER JOIN dbo_OF_LineItemDetail ON"
sql = sql + " (dbo_OF_TransmissionHdr.messageControlNumber = "
sql = sql + " dbo_OF_LineItemDetail.messageControlNumber) "
sql = sql + " AND (dbo_OF_TransmissionHdr.transmissionControlNumber ="
sql = sql + " dbo_OF_LineItemDetail.transmissionControlNumber))"                   '<Ending parentesis for first join
sql = sql + " INNER JOIN tblDotComCartonMaster ON"
sql = sql + " (dbo_OF_TransmissionHdr.messageControlNumber = "
sql = sql + " tblDotComCartonMaster.messageControlNumber) "
sql = sql + " AND (dbo_OF_TransmissionHdr.transmissionControlNumber ="
sql = sql + " tblDotComCartonMaster.transmissionControlNumber)"
will_scarlet7Commented:
Oops... Sorry, I added an extra line:

sql = sql + " FROM (dbo_OF_TransmissionHdr "                                                '<Starting parenthesis for first join
sql = sql + " INNER JOIN dbo_OF_LineItemDetail ON"
sql = sql + " (dbo_OF_TransmissionHdr.messageControlNumber = "
sql = sql + " dbo_OF_LineItemDetail.messageControlNumber) "
sql = sql + " AND (dbo_OF_TransmissionHdr.transmissionControlNumber ="
sql = sql + " dbo_OF_LineItemDetail.transmissionControlNumber))"                   '<Ending parenthesis for first join
sql = sql + " INNER JOIN tblDotComCartonMaster ON"
sql = sql + " (dbo_OF_TransmissionHdr.messageControlNumber = "
sql = sql + " tblDotComCartonMaster.messageControlNumber) "
sql = sql + " AND (dbo_OF_TransmissionHdr.transmissionControlNumber ="
sql = sql + " tblDotComCartonMaster.transmissionControlNumber)"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

GRayLCommented:
Change:

#sql = sql + " tblDotComCartonMaster.transmissionControlNumber)"

to:

#sql = sql + " tblDotComCartonMaster.transmissionControlNumber) "
GRayLCommented:
Okay, so I'm vain.  Disregard!  Hard to tell with this font if the start of the next line is <dblquote><space> or just <dblquote> ;-)
GRayLCommented:
The concatination character in Access is "&", not "+".  Try changing

sql = sql + "

To:

sql = sql & "
will_scarlet7Commented:
chantalcookware,
You've been kinda silent... Any feedback?
chantalcookwareAuthor Commented:
Hi,
I added the parenthsis to the first join and got a syntax error on the FROM clause. Any ideas. Here is the revised code:
sql = "SELECT "
sql = sql + " dbo_OF_TransmissionHdr.isGift,"
sql = sql + " dbo_OF_TransmissionHdr.MacolaOrderNo, "
sql = sql + " dbo_OF_TransmissionHdr.SendEmailFlag, "
sql = sql + " dbo_OF_TransmissionHdr.transmissionControlNumber, "
sql = sql + " dbo_OF_TransmissionHdr.messageControlNumber, "
sql = sql + " dbo_OF_TransmissionHdr.PurchaseOrderNumber, "
sql = sql + " dbo_OF_TransmissionHdr.ShipMethod, "
sql = sql + " dbo_OF_LineItemDetail.ItemId, "
sql = sql + " dbo_OF_LineItemDetail.productName, "
sql = sql + " dbo_OF_LineItemDetail.QuantityToShip, "
sql = sql + " tblDotComCartonMaster.TrackingID"
sql = sql + " FROM (dbo_OF_TransmissionHdr "
sql = sql + " INNER JOIN dbo_OF_LineItemDetail ON"
sql = sql + " (dbo_OF_TransmissionHdr.messageControlNumber = "
sql = sql + " dbo_OF_LineItemDetail.messageControlNumber) "
sql = sql + " AND (dbo_OF_TransmissionHdr.transmissionControlNumber ="
sql = sql + " dbo_OF_LineItemDetail.transmissionControlNumber))"
sql = sql + " AND INNER JOIN tblDotComCartonMaster ON"
sql = sql + " (dbo_OF_TransmissionHdr.messageControlNumber = "
sql = sql + " tblDotComCartonMaster.messageControlNumber) "
sql = sql + " AND (dbo_OF_TransmissionHdr.transmissionControlNumber ="
sql = sql + " tblDotComCartonMaster.transmissionControlNumber)"
sql = sql + " WHERE (((dbo_OF_TransmissionHdr.SendEmailFlag)=False) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.ResponseCode)='00') "
sql = sql + " AND ((dbo_OF_TransmissionHdr.killFlag)=False) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.DupePOFlag)=False) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.PickListPrinted)=True) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.OrderCompleteFlag)=True) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.PackListPrinted)=True))"
sql = sql + " ORDER BY dbo_OF_TransmissionHdr.MacolaOrderNo; "
GRayLCommented:
I would remove all parentheses in lines between FROM and WHERE.  Remove the AND before the INNER JOIN and change the concatination character in ever line to "&".
GRayLCommented:
Actually you are far better off developing a query like this in the Query Builderand get it to run before attempting to break down the string into components.
GRayLCommented:
Use of aliases can really improve the readability.  Don't you find this easier to debug:

sql = "SELECT "
sql = sql & " a.isGift,"
sql = sql & " a.MacolaOrderNo, "
sql = sql & " a.SendEmailFlag, "
sql = sql & " a.transmissionControlNumber, "
sql = sql & " a.messageControlNumber, "
sql = sql & " a.PurchaseOrderNumber, "
sql = sql & " a.ShipMethod, "
sql = sql & " b.ItemId, "
sql = sql & " b.productName, "
sql = sql & " b.QuantityToShip, "
sql = sql & " c.TrackingID"
sql = sql & " FROM dbo_OF_TransmissionHdr a "
sql = sql & " INNER JOIN dbo_OF_LineItemDetail b ON"
sql = sql & " a.messageControlNumber = "
sql = sql & " b.messageControlNumber "
sql = sql & " AND a.transmissionControlNumber ="
sql = sql & " b.transmissionControlNumber"
sql = sql & " INNER JOIN tblDotComCartonMaster c ON"
sql = sql & " a.messageControlNumber = "
sql = sql & " c.messageControlNumber "
sql = sql & " AND (a.transmissionControlNumber ="
sql = sql & " c.transmissionControlNumber"
sql = sql & " WHERE (((a.SendEmailFlag)=False) "
sql = sql & " AND ((a.ResponseCode)='00') "
sql = sql & " AND ((a.killFlag)=False) "
sql = sql & " AND ((a.DupePOFlag)=False) "
sql = sql & " AND ((a.PickListPrinted)=True) "
sql = sql & " AND ((a.OrderCompleteFlag)=True) "
sql = sql & " AND ((a.PackListPrinted)=True))"
sql = sql & " ORDER BY a.MacolaOrderNo; "
will_scarlet7Commented:
I appreciate the points! I hope I was a help.
God bless!

Sam
GRayLCommented:
Thanks.  g'morning Sam.
will_scarlet7Commented:
Good evening Ray : )
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.