Link to home
Start Free TrialLog in
Avatar of chantalcookware
chantalcookware

asked on

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; "
Avatar of kirenievs
kirenievs
Flag of Sweden image

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, "

Avatar of will_scarlet7
will_scarlet7

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)"
ASKER CERTIFIED SOLUTION
Avatar of will_scarlet7
will_scarlet7

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Change:

#sql = sql + " tblDotComCartonMaster.transmissionControlNumber)"

to:

#sql = sql + " tblDotComCartonMaster.transmissionControlNumber) "
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> ;-)
The concatination character in Access is "&", not "+".  Try changing

sql = sql + "

To:

sql = sql & "
chantalcookware,
You've been kinda silent... Any feedback?
Avatar of chantalcookware

ASKER

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; "
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 "&".
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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; "
I appreciate the points! I hope I was a help.
God bless!

Sam
Thanks.  g'morning Sam.
Good evening Ray : )