SQL statement in VBA (1 Viewer)

Hi Paul,

I agree - line continuation is more efficient, makes the SQL easier to read, and is a personal preference.

I do think the concatenation method is easier for people who do not have strong SQL skills. The auto-compiler will show a syntax error on the specific line, opposed to the entire multi-line statement created by using the line continuation operator.

Thanks for the tips I walked away from this yesterday and came in with fresh eyes and found the errors

I had an extra comma (,) just before the FROM clause and during all my changes removed the space before the word FROM. It works now


SQL = "SELECT tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street]," _
& "tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number]," _
& "tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material," _
& "tblDetails.[Material Desc from SO line] , tblInstallSummary.SOI" _
& " FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number])" _
& " LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1" _
& " WHERE " & strCriteria & " ORDER BY tblDetails.[Item Number];"

Glad you got it fixed. Odd those didn't show up in the Debug.Print though.