How to break long sql vba statement?

**puuts** · 02-09-2015, 02:13 PM

I have the below code and I need to break it into multiple lines


SqlQuery = "SELECT COUNT(*) AS Report_ID,q1.Report_Name,q1.Report_Owner,q1.Function,q1.Region,q1.Comment,q1.Cut_Off,q1.Reminder_Time,q1.Report_Day FROM (SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day FROM tbl_Monday UNION ALL SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day FROM tbl_Tuesday UNION ALL SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day FROM tbl_Wednesday) q1 INNER JOIN(SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day FROM tbl_Monday UNION ALL SELECT" & _
            "Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day FROM tbl_Tuesday UNION ALL SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day FROM tbl_Wednesday)" & _
            "q2 on CStr(q1.Report_ID) + q1.Report_Name + q1.Report_Owner + q1.Function + q1.Region + q1.Comment + q1.Cut_Off + q1.Reminder_Time + q1.Reminder_Day" & _
            ">= CStr(q2.Report_ID) + q2.Report_Name + q2.Report_Owner GROUP BY q1.Report_ID,q1.Report_Name,q1.Report_Owner"

**Norie** · 02-09-2015, 02:23 PM

Perhaps something like this.

    SqlQuery = "SELECT COUNT(*) AS Report_ID,q1.Report_Name,q1.Report_Owner,q1.Function,q1.Region,q1.Comment,q1.Cut_Off,q1.Reminder_Time,q1.Report_Day "
    SqlQuery = SqlQuery & " FROM (SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day "
    SqlQuery = SqlQuery & " FROM tbl_Monday "
    SqlQuery = SqlQuery & " UNION ALL SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day "
    SqlQuery = SqlQuery & " FROM tbl_Tuesday "
    SqlQuery = SqlQuery & " UNION ALL SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day "
    SqlQuery = SqlQuery & " FROM tbl_Wednesday) q1 "
    SqlQuery = SqlQuery & " INNER JOIN(SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day "
    SqlQuery = SqlQuery & " FROM tbl_Monday "
    SqlQuery = SqlQuery & " UNION ALL SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day "
    SqlQuery = SqlQuery & " FROM tbl_Tuesday "
    SqlQuery = SqlQuery & " UNION ALL SELECT Report_ID,Report_Name,Report_Owner,Function,Region,Comment,Cut_Off,Reminder_Time,Report_Day "
    SqlQuery = SqlQuery & " FROM tbl_Wednesday) q2 "
    SqlQuery = SqlQuery & " ON CStr(q1.Report_ID) + q1.Report_Name + q1.Report_Owner + q1.Function + q1.Region + q1.Comment + q1.Cut_Off + q1.Reminder_Time + q1.Reminder_Day"
    SqlQuery = SqlQuery & ">= CStr(q2.Report_ID) + q2.Report_Name + q2.Report_Owner GROUP BY q1.Report_ID,q1.Report_Name,q1.Report_Owner"