Hey I was wondering if someone here can help me. I have a very long sql statement but I'm running into errors when trying to run as part of my script. My normal sql statements are like
Code:
' Set SQL statement
'    strSQL = "SELECT custno, Company, Contact, Address1, Address2, "
'    strSQL = strSQL & "City, State , Zip, Country, Phone, Email, "
'    strSQL = strSQL & "shipvia, ponum "
'    strSQL = strSQL & "FROM " & TABLE_NAME
'    strSQL = strSQL & " WHERE " & ODBC1_FIELD_NAME & " = '" & strKeyValue & "'"
The problem is that now I have a very large statement and I'm lost as how to get the syntax correct so it will run in VBA. It works as is in razorSQL. I can't get it to work in WinSql. My question is HOW and WHERE do I add the
strSQL = "strSQL = " so it works in VBA?

Code:
SELECT TOP 1
                ShipHead.PackNum,
                ShipHead.ShipViaCode,
                ShipHead.NotifyEMail,
                OrderRel.OrderNum,
                CASE WHEN OrderRel.UseOTS = 0 THEN CustCnt.Name ELSE OrderRel.OTSContact END AS STContact,
                CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Name ELSE OrderRel.OTSName END AS STName,
                CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Address1 ELSE OrderRel.OTSAddress1 END AS STAdd1,
                CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Address2 ELSE OrderRel.OTSAddress2 END AS STAdd2,
                CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Address3 ELSE OrderRel.OTSAddress3 END AS STAdd3,
                CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.City ELSE OrderRel.OTSCity END AS STCity,
                CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.State ELSE OrderRel.OTSState END AS STState,
                CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.ZIP ELSE OrderRel.OTSZIP END AS STZIP,
                CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.PhoneNum ELSE OrderRel.OTSPhoneNum END AS STPhoneNum,
                CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.CountryNum ELSE OrderRel.OTSCountryNum END AS STCountryNum
FROM
                MFGSYS.PUB.ShipHead ShipHead
                JOIN MFGSYS.PUB.ShipDtl ShipDtl ON ShipHead.Company = ShipDtl.Company AND ShipHead.PackNum = ShipDtl.PackNum
                JOIN MFGSYS.PUB.OrderRel OrderRel ON ShipDtl.Company = OrderRel.Company AND ShipDtl.OrderNum = OrderRel.OrderNum
                JOIN MFGSYS.PUB.ShipTo ShipTo ON OrderRel.ShipToNum = ShipTo.ShipToNum AND OrderRel.ShipToCustNum = ShipTo.CustNum
                LEFT JOIN MFGSYS.PUB.CustCnt CustCnt ON OrderRel.ShipToCustNum = CustCnt.CustNum AND OrderRel.ShpConNum = CustCnt.ConNum
WHERE
                ShipHead.Company = '68138' AND
                ShipHead.PackNum = strval
Thanks Bryan