Help with VBA SQL query

[Excellover123]

Hello Message board.

I have a query that someone built years ago & I'm trying to get something different using the same method.

Exiting Code

I'm really struggling to get the new code to work. Any ideas folk?

Kind regards, Gary

 

[aaewalsh]

Please post the code. Pictures are not helpful

 

[Excellover123]

Please post the code. Pictures are not helpful

Morning, aarwalsh - Thank you for your reply - Please see below - Please let me know if you need anything else?

Existing Code

Query = "SELECT T0.VendorRescVResc, T2.Description, T1.Description, T3.UnitPriceCurAmt, T3.EffDatesStart, T3.EffDatesEnd, T3.UnitPriceCur, T0.PurchPerct " & _
"FROM " & DSN & ".dbo.poVendorResourc T0, " & DSN & ".dbo.fdTradingPartne T1, " & DSN & ".dbo.fdBasResc T2, " & DSN & ".dbo.poVendRescPric T3 " & _
"WHERE T1.ObjectID = T0.VendorRescTradPOID AND T2.ObjectID = T0.VendorRescVRescOID AND T3.ParentObjectID = T0.ObjectID AND ((T0.InActive=0) " & _
"AND (T3.EffDatesStart<={ts '" & EffDate & "'}) AND (T3.EffDatesEnd>={ts '" & EffDate & "'})) " & _
"ORDER BY T0.VendorRescVResc, T0.PurchPerct ASC"

New code

SELECT T0.ObjectID, T0.SecGrp1ObjectID, T0.SecGrp2ObjectID, T0.SecGrp3ObjectID, T0.SecGrp4ObjectID, T0.SecGrp5ObjectID, T0.AdminSite, T0.IconName, T0.POInstType, T0.POSite, T0.PO, T2.ReqDelDt, T0.OrderFrTP, T3.Description
FROM poPurchaseOrder T0
JOIN poLine T1 ON T1.ParentObjectID = T0.ObjectID AND T1.ParentClassID = 11588 AND T1.CollectionID = 1
JOIN poDelivery T2 ON T2.ParentObjectID = T1.ObjectID AND T2.ParentClassID = 11608 AND T2.CollectionID = 1
JOIN fdTradingPartne T3 ON T3.ObjectID = T0.OrderFrTPObjectID
WHERE (T2.ReqDelDt >= {ts '2021-01-01 00:00:00'} AND T2.ReqDelDt <= {ts '2035-12-31 23:59:59'})
AND T1.LineType = 1

 

[aaewalsh]

Morning, aarwalsh - Thank you for your reply - Please see below - Please let me know if you need anything else?

Existing Code

Query = "SELECT T0.VendorRescVResc, T2.Description, T1.Description, T3.UnitPriceCurAmt, T3.EffDatesStart, T3.EffDatesEnd, T3.UnitPriceCur, T0.PurchPerct " & _
"FROM " & DSN & ".dbo.poVendorResourc T0, " & DSN & ".dbo.fdTradingPartne T1, " & DSN & ".dbo.fdBasResc T2, " & DSN & ".dbo.poVendRescPric T3 " & _
"WHERE T1.ObjectID = T0.VendorRescTradPOID AND T2.ObjectID = T0.VendorRescVRescOID AND T3.ParentObjectID = T0.ObjectID AND ((T0.InActive=0) " & _
"AND (T3.EffDatesStart<={ts '" & EffDate & "'}) AND (T3.EffDatesEnd>={ts '" & EffDate & "'})) " & _
"ORDER BY T0.VendorRescVResc, T0.PurchPerct ASC"

New code

SELECT T0.ObjectID, T0.SecGrp1ObjectID, T0.SecGrp2ObjectID, T0.SecGrp3ObjectID, T0.SecGrp4ObjectID, T0.SecGrp5ObjectID, T0.AdminSite, T0.IconName, T0.POInstType, T0.POSite, T0.PO, T2.ReqDelDt, T0.OrderFrTP, T3.Description
FROM poPurchaseOrder T0
JOIN poLine T1 ON T1.ParentObjectID = T0.ObjectID AND T1.ParentClassID = 11588 AND T1.CollectionID = 1
JOIN poDelivery T2 ON T2.ParentObjectID = T1.ObjectID AND T2.ParentClassID = 11608 AND T2.CollectionID = 1
JOIN fdTradingPartne T3 ON T3.ObjectID = T0.OrderFrTPObjectID
WHERE (T2.ReqDelDt >= {ts '2021-01-01 00:00:00'} AND T2.ReqDelDt <= {ts '2035-12-31 23:59:59'})
AND T1.LineType = 1

wat error messages are you getting

 

[Excellover123]

wat error messages are you getting

It was all highlighting in error in my VBA code. Think it's because the existing code is wrapped in many "" & the new code isn't ?