Originally Posted by
June7
twgonder,
Debug.Print compiled string variable if need to copy/paste to test in query object.
....
That works great for getting SQL out of VBA, getting it into is a whole other story.
Here's a smaller one that I had recently done, and every time I tried to chop it up, I made a mess of it that wouldn't compile.
Code:
DefBegSql = "SELECT tbl_3_Tranlt.ID, tbl_3_Tranlt.ClientID, tbl_4_Entity.Nm1, tbl_3_Tranlt.AppModID, tbl_3_Application.DescS, tbl_3_Tranlt.TrTypID, tblu_3_Element.DescS, tbl_3_Tranlt.TrCd, tbl_3_TranltLang.ID, tbl_3_TranltLang.LangID, [tbl_3_TranltLang.LblFrm] & "";"" & [tbl_3_TranltLang.LblRpt] & "";"" & [tbl_3_TranltLang.Hlp] & "";"" & [tbl_3_TranltLang.HlpL] AS LblsMsgHlp FROM tbl_4_Entity INNER JOIN (tblu_3_Element INNER JOIN (tbl_3_Application INNER JOIN (tbl_3_Client INNER JOIN (tbl_3_Tranlt LEFT JOIN tbl_3_TranltLang ON tbl_3_Tranlt.ID = tbl_3_TranltLang.TranltID) ON tbl_3_Client.ID = tbl_3_Tranlt.ClientID) ON tbl_3_Application.ID = tbl_3_Tranlt.AppModID) ON tblu_3_Element.ID = tbl_3_Tranlt.TrTypID) ON tbl_4_Entity.ID = tbl_3_Client.EntityId"
After 30 minutes of this, I said "F-it, just copy the SQL out of a query, fix the single quote to double and get on with it." It's not like I'm going to try to read it and fix that monster within VBA. That's my new rule--works for me.
Besides, I went cross-eyed and got an Advil headache from looking up and down and up and down between the lines.