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
        

Open in new window

al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

al4629740Author Commented:
as you can see, the bold did not work for me but I think you can see the variables that need attention
0
sirbountyCommented:
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            
                    

Open in new window

0
royehSoftware EngineerCommented:
Open 3 recordsets, and loop through them all:

E.G.

'Get List of 8 to 12 and under--------
       esql1 = "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"
                
'Get List of 13 to 17 and under--------
       esql2 = "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"
                
'Get List of 18 to 20 and under--------
       esql3 = "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 (esql1), conn, adOpenDynamic, adLockOptimistic
       rec2.Open (esql2), conn, adOpenDynamic, adLockOptimistic
       rec3.Open (esql3), conn, adOpenDynamic, adLockOptimistic

        i = 6
        
        Do Until rec1.EOF = True And rec2.EOF = True And rec3.EOF = True
	
                If rec1.EOF = False Then ApExcel.Workbooks("CYSReport.xls").Sheets("Direct Service").Cells(i, [b]12[/b]).Formula = rec1.Fields(1)
                If rec2.EOF = False Then ApExcel.Workbooks("CYSReport.xls").Sheets("Direct Service").Cells(i, [b]13[/b]).Formula = rec2.Fields(1)
                If rec3.EOF = False Then ApExcel.Workbooks("CYSReport.xls").Sheets("Direct Service").Cells(i, [b]14[/b]).Formula = rec3.Fields(1)
                
                i = i + 2

                If rec1.EOF = False Then rec1.MoveNext
                If rec2.EOF = False Then rec2.MoveNext
                If rec3.EOF = False Then rec3.MoveNext
        
        Loop
                
        rec1.Close
        rec2.Close
        rec3.Close

Open in new window


You'll need to double-check the EOF flags, because you have multiple recordsets.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.