condense 3 loops into 1
This code has the same iterations 3 times. How can I keep only one loop and simply modify only the 3 variables in bold. I don't see a pattern for the ages that would work as I was thinking of putting a loop within a loop.
'Get List of 8 to 12 and under--------
esql = "DECLARE @fromdt AS datetime " & _
"DECLARE @todt AS datetime " & _
"SET @fromdt = '" & DTPicker1 & "' " & _
"SET @todt = '" & DTPicker2 & "' " & _
"SELECT p.Agency, count(distinct r.RegID) " & _
"FROM tblOrgProfile as p " & _
"LEFT JOIN tblOrgRegistrations as r " & _
"ON p.AgencyID = r.AgencyID " & _
"AND r.AgeRegistration >= [b]8 [/b]" & _
"AND r.AgeRegistration <= [b]12 [/b]" & _
"LEFT JOIN tblOrgHours as h " & _
"ON h.RegID = r.regid " & _
"AND h.ActivityDate >= @fromdt " & _
"AND h.ActivityDate <= @todt " & _
"GROUP BY p.Agency " & _
"ORDER BY p.Agency"
rec1.Open (esql), conn, adOpenDynamic, adLockOptimistic
i = 6
Do Until rec1.EOF = True
ApExcel.Workbooks("CYSReport.xls").Sheets("Direct Service").Cells(i, [b]12[/b]).Formula = rec1.Fields(1)
i = i + 2
rec1.MoveNext
Loop
rec1.Close
'Get List of 13 to 17 and under--------
esql = "DECLARE @fromdt AS datetime " & _
"DECLARE @todt AS datetime " & _
"SET @fromdt = '" & DTPicker1 & "' " & _
"SET @todt = '" & DTPicker2 & "' " & _
"SELECT p.Agency, count(distinct r.RegID) " & _
"FROM tblOrgProfile as p " & _
"LEFT JOIN tblOrgRegistrations as r " & _
"ON p.AgencyID = r.AgencyID " & _
"AND r.AgeRegistration >= [b]13 [/b]" & _
"AND r.AgeRegistration <= [b]17 [/b]" & _
"LEFT JOIN tblOrgHours as h " & _
"ON h.RegID = r.regid " & _
"AND h.ActivityDate >= @fromdt " & _
"AND h.ActivityDate <= @todt " & _
"GROUP BY p.Agency " & _
"ORDER BY p.Agency"
rec1.Open (esql), conn, adOpenDynamic, adLockOptimistic
i = 6
Do Until rec1.EOF = True
ApExcel.Workbooks("CYSReport.xls").Sheets("Direct Service").Cells(i, [b]13[/b]).Formula = rec1.Fields(1)
i = i + 2
rec1.MoveNext
Loop
rec1.Close
'Get List of 18 to 20 and under--------
esql = "DECLARE @fromdt AS datetime " & _
"DECLARE @todt AS datetime " & _
"SET @fromdt = '" & DTPicker1 & "' " & _
"SET @todt = '" & DTPicker2 & "' " & _
"SELECT p.Agency, count(distinct r.RegID) " & _
"FROM tblOrgProfile as p " & _
"LEFT JOIN tblOrgRegistrations as r " & _
"ON p.AgencyID = r.AgencyID " & _
"AND r.AgeRegistration >= [b]18 [/b]" & _
"AND r.AgeRegistration <= [b]20 [/b]" & _
"LEFT JOIN tblOrgHours as h " & _
"ON h.RegID = r.regid " & _
"AND h.ActivityDate >= @fromdt " & _
"AND h.ActivityDate <= @todt " & _
"GROUP BY p.Agency " & _
"ORDER BY p.Agency"
rec1.Open (esql), conn, adOpenDynamic, adLockOptimistic
i = 6
Do Until rec1.EOF = True
ApExcel.Workbooks("CYSReport.xls").Sheets("Direct Service").Cells(i, [b]14[/b]).Formula = rec1.Fields(1)
i = i + 2
rec1.MoveNext
Loop
rec1.Close
Something like this could work:
'Get List of 8 to 12 and under--------
for x as int16 = 8 to 22 step 5
if x = 18 then
y = 20
else
y = x + 4
end if
esql = "DECLARE @fromdt AS datetime " & _
"DECLARE @todt AS datetime " & _
"SET @fromdt = '" & DTPicker1 & "' " & _
"SET @todt = '" & DTPicker2 & "' " & _
"SELECT p.Agency, count(distinct r.RegID) " & _
"FROM tblOrgProfile as p " & _
"LEFT JOIN tblOrgRegistrations as r " & _
"ON p.AgencyID = r.AgencyID " & _
"AND r.AgeRegistration >= " & x & " & _
"AND r.AgeRegistration <= " & y & " & _
"LEFT JOIN tblOrgHours as h " & _
"ON h.RegID = r.regid " & _
"AND h.ActivityDate >= @fromdt " & _
"AND h.ActivityDate <= @todt " & _
"GROUP BY p.Agency " & _
"ORDER BY p.Agency"
rec1.Open (esql), conn, adOpenDynamic, adLockOptimistic
i = 6
Do Until rec1.EOF = True
ApExcel.Workbooks("CYSReport.xls").Sheets("Direct Service").Cells(i, [b]12[/b]).Formula = rec1.Fields(1)
i = i + 2
rec1.MoveNext
Loop
rec1.Close
next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER