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.isG ift,"
sql = sql + " dbo_OF_TransmissionHdr.Mac olaOrderNo , "
sql = sql + " dbo_OF_TransmissionHdr.Sen dEmailFlag , "
sql = sql + " dbo_OF_TransmissionHdr.tra nsmissionC ontrolNumb er, "
sql = sql + " dbo_OF_TransmissionHdr.mes sageContro lNumber, "
sql = sql + " dbo_OF_TransmissionHdr.Pur chaseOrder Number, "
sql = sql + " dbo_OF_TransmissionHdr.Shi pMethod, "
sql = sql + " dbo_OF_LineItemDetail.Item Id, "
sql = sql + " dbo_OF_LineItemDetail.prod uctName, "
sql = sql + " dbo_OF_LineItemDetail.Quan tityToShip , "
sql = sql + " tblDotComCartonMaster.Trac kingID"
sql = sql + " FROM dbo_OF_TransmissionHdr "
sql = sql + " INNER JOIN dbo_OF_LineItemDetail ON"
sql = sql + " (dbo_OF_TransmissionHdr.me ssageContr olNumber = "
sql = sql + " dbo_OF_LineItemDetail.mess ageControl Number) "
sql = sql + " AND (dbo_OF_TransmissionHdr.tr ansmission ControlNum ber ="
sql = sql + " dbo_OF_LineItemDetail.tran smissionCo ntrolNumbe r)"
#sql = sql + " INNER JOIN tblDotComCartonMaster ON"
#sql = sql + " (dbo_OF_TransmissionHdr.me ssageContr olNumber = "
#sql = sql + " tblDotComCartonMaster.mess ageControl Number) "
#sql = sql + " AND (dbo_OF_TransmissionHdr.tr ansmission ControlNum ber ="
#sql = sql + " tblDotComCartonMaster.tran smissionCo ntrolNumbe r)"
sql = sql + " WHERE (((dbo_OF_TransmissionHdr. SendEmailF lag)=False ) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.R esponseCod e)='00') "
sql = sql + " AND ((dbo_OF_TransmissionHdr.k illFlag)=F alse) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.D upePOFlag) =False) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.P ickListPri nted)=True ) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.O rderComple teFlag)=Tr ue) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.P ackListPri nted)=True ))"
sql = sql + " ORDER BY dbo_OF_TransmissionHdr.Mac olaOrderNo ; "
sql = "SELECT "
sql = sql + " dbo_OF_TransmissionHdr.isG
sql = sql + " dbo_OF_TransmissionHdr.Mac
sql = sql + " dbo_OF_TransmissionHdr.Sen
sql = sql + " dbo_OF_TransmissionHdr.tra
sql = sql + " dbo_OF_TransmissionHdr.mes
sql = sql + " dbo_OF_TransmissionHdr.Pur
sql = sql + " dbo_OF_TransmissionHdr.Shi
sql = sql + " dbo_OF_LineItemDetail.Item
sql = sql + " dbo_OF_LineItemDetail.prod
sql = sql + " dbo_OF_LineItemDetail.Quan
sql = sql + " tblDotComCartonMaster.Trac
sql = sql + " FROM dbo_OF_TransmissionHdr "
sql = sql + " INNER JOIN dbo_OF_LineItemDetail ON"
sql = sql + " (dbo_OF_TransmissionHdr.me
sql = sql + " dbo_OF_LineItemDetail.mess
sql = sql + " AND (dbo_OF_TransmissionHdr.tr
sql = sql + " dbo_OF_LineItemDetail.tran
#sql = sql + " INNER JOIN tblDotComCartonMaster ON"
#sql = sql + " (dbo_OF_TransmissionHdr.me
#sql = sql + " tblDotComCartonMaster.mess
#sql = sql + " AND (dbo_OF_TransmissionHdr.tr
#sql = sql + " tblDotComCartonMaster.tran
sql = sql + " WHERE (((dbo_OF_TransmissionHdr.
sql = sql + " AND ((dbo_OF_TransmissionHdr.R
sql = sql + " AND ((dbo_OF_TransmissionHdr.k
sql = sql + " AND ((dbo_OF_TransmissionHdr.D
sql = sql + " AND ((dbo_OF_TransmissionHdr.P
sql = sql + " AND ((dbo_OF_TransmissionHdr.O
sql = sql + " AND ((dbo_OF_TransmissionHdr.P
sql = sql + " ORDER BY dbo_OF_TransmissionHdr.Mac
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.me ssageContr olNumber = "
sql = sql + " dbo_OF_LineItemDetail.mess ageControl Number) "
sql = sql + " AND (dbo_OF_TransmissionHdr.tr ansmission ControlNum ber ="
sql = sql + " dbo_OF_LineItemDetail.tran smissionCo ntrolNumbe r))" '<Ending parentesis for first join
sql = sql + " INNER JOIN tblDotComCartonMaster ON"
sql = sql + " (dbo_OF_TransmissionHdr.me ssageContr olNumber = "
sql = sql + " tblDotComCartonMaster.mess ageControl Number) "
sql = sql + " AND (dbo_OF_TransmissionHdr.tr ansmission ControlNum ber ="
sql = sql + " tblDotComCartonMaster.tran smissionCo ntrolNumbe r)"
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.me
sql = sql + " dbo_OF_LineItemDetail.mess
sql = sql + " AND (dbo_OF_TransmissionHdr.tr
sql = sql + " dbo_OF_LineItemDetail.tran
sql = sql + " INNER JOIN tblDotComCartonMaster ON"
sql = sql + " (dbo_OF_TransmissionHdr.me
sql = sql + " tblDotComCartonMaster.mess
sql = sql + " AND (dbo_OF_TransmissionHdr.tr
sql = sql + " tblDotComCartonMaster.tran
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Change:
#sql = sql + " tblDotComCartonMaster.tran smissionCo ntrolNumbe r)"
to:
#sql = sql + " tblDotComCartonMaster.tran smissionCo ntrolNumbe r) "
#sql = sql + " tblDotComCartonMaster.tran
to:
#sql = sql + " tblDotComCartonMaster.tran
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 & "
sql = sql + "
To:
sql = sql & "
chantalcookware,
You've been kinda silent... Any feedback?
You've been kinda silent... Any feedback?
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.isG ift,"
sql = sql + " dbo_OF_TransmissionHdr.Mac olaOrderNo , "
sql = sql + " dbo_OF_TransmissionHdr.Sen dEmailFlag , "
sql = sql + " dbo_OF_TransmissionHdr.tra nsmissionC ontrolNumb er, "
sql = sql + " dbo_OF_TransmissionHdr.mes sageContro lNumber, "
sql = sql + " dbo_OF_TransmissionHdr.Pur chaseOrder Number, "
sql = sql + " dbo_OF_TransmissionHdr.Shi pMethod, "
sql = sql + " dbo_OF_LineItemDetail.Item Id, "
sql = sql + " dbo_OF_LineItemDetail.prod uctName, "
sql = sql + " dbo_OF_LineItemDetail.Quan tityToShip , "
sql = sql + " tblDotComCartonMaster.Trac kingID"
sql = sql + " FROM (dbo_OF_TransmissionHdr "
sql = sql + " INNER JOIN dbo_OF_LineItemDetail ON"
sql = sql + " (dbo_OF_TransmissionHdr.me ssageContr olNumber = "
sql = sql + " dbo_OF_LineItemDetail.mess ageControl Number) "
sql = sql + " AND (dbo_OF_TransmissionHdr.tr ansmission ControlNum ber ="
sql = sql + " dbo_OF_LineItemDetail.tran smissionCo ntrolNumbe r))"
sql = sql + " AND INNER JOIN tblDotComCartonMaster ON"
sql = sql + " (dbo_OF_TransmissionHdr.me ssageContr olNumber = "
sql = sql + " tblDotComCartonMaster.mess ageControl Number) "
sql = sql + " AND (dbo_OF_TransmissionHdr.tr ansmission ControlNum ber ="
sql = sql + " tblDotComCartonMaster.tran smissionCo ntrolNumbe r)"
sql = sql + " WHERE (((dbo_OF_TransmissionHdr. SendEmailF lag)=False ) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.R esponseCod e)='00') "
sql = sql + " AND ((dbo_OF_TransmissionHdr.k illFlag)=F alse) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.D upePOFlag) =False) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.P ickListPri nted)=True ) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.O rderComple teFlag)=Tr ue) "
sql = sql + " AND ((dbo_OF_TransmissionHdr.P ackListPri nted)=True ))"
sql = sql + " ORDER BY dbo_OF_TransmissionHdr.Mac olaOrderNo ; "
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.isG
sql = sql + " dbo_OF_TransmissionHdr.Mac
sql = sql + " dbo_OF_TransmissionHdr.Sen
sql = sql + " dbo_OF_TransmissionHdr.tra
sql = sql + " dbo_OF_TransmissionHdr.mes
sql = sql + " dbo_OF_TransmissionHdr.Pur
sql = sql + " dbo_OF_TransmissionHdr.Shi
sql = sql + " dbo_OF_LineItemDetail.Item
sql = sql + " dbo_OF_LineItemDetail.prod
sql = sql + " dbo_OF_LineItemDetail.Quan
sql = sql + " tblDotComCartonMaster.Trac
sql = sql + " FROM (dbo_OF_TransmissionHdr "
sql = sql + " INNER JOIN dbo_OF_LineItemDetail ON"
sql = sql + " (dbo_OF_TransmissionHdr.me
sql = sql + " dbo_OF_LineItemDetail.mess
sql = sql + " AND (dbo_OF_TransmissionHdr.tr
sql = sql + " dbo_OF_LineItemDetail.tran
sql = sql + " AND INNER JOIN tblDotComCartonMaster ON"
sql = sql + " (dbo_OF_TransmissionHdr.me
sql = sql + " tblDotComCartonMaster.mess
sql = sql + " AND (dbo_OF_TransmissionHdr.tr
sql = sql + " tblDotComCartonMaster.tran
sql = sql + " WHERE (((dbo_OF_TransmissionHdr.
sql = sql + " AND ((dbo_OF_TransmissionHdr.R
sql = sql + " AND ((dbo_OF_TransmissionHdr.k
sql = sql + " AND ((dbo_OF_TransmissionHdr.D
sql = sql + " AND ((dbo_OF_TransmissionHdr.P
sql = sql + " AND ((dbo_OF_TransmissionHdr.O
sql = sql + " AND ((dbo_OF_TransmissionHdr.P
sql = sql + " ORDER BY dbo_OF_TransmissionHdr.Mac
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.transmissionControlNumbe r, "
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.transmissionControlNumbe r ="
sql = sql & " b.transmissionControlNumbe r"
sql = sql & " INNER JOIN tblDotComCartonMaster c ON"
sql = sql & " a.messageControlNumber = "
sql = sql & " c.messageControlNumber "
sql = sql & " AND (a.transmissionControlNumb er ="
sql = sql & " c.transmissionControlNumbe r"
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)=Tru e) "
sql = sql & " AND ((a.PackListPrinted)=True) )"
sql = sql & " ORDER BY a.MacolaOrderNo; "
sql = "SELECT "
sql = sql & " a.isGift,"
sql = sql & " a.MacolaOrderNo, "
sql = sql & " a.SendEmailFlag, "
sql = sql & " a.transmissionControlNumbe
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.transmissionControlNumbe
sql = sql & " b.transmissionControlNumbe
sql = sql & " INNER JOIN tblDotComCartonMaster c ON"
sql = sql & " a.messageControlNumber = "
sql = sql & " c.messageControlNumber "
sql = sql & " AND (a.transmissionControlNumb
sql = sql & " c.transmissionControlNumbe
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)=Tru
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
God bless!
Sam
Thanks. g'morning Sam.
Good evening Ray : )
Another comment: You will get less typing using this way:
sql = "SELECT " _
& " dbo_OF_TransmissionHdr.isG
& " dbo_OF_TransmissionHdr.Mac