Loop months of the year in this code

I am trying to loop 3 months of the year into the code below.  How do I write this type of loop statement.  I am drawing a blank for some reason...


'How turn this into a loop where Var1 is a loop of January, February, March so that I get the total for all 3 months

select count(*) from table where Month = Var1
rec2.CursorLocation = adUseClient
rec2.Open (esql), conn, adOpenStatic, adLockOptimistic

Total = Total + rec.Fields(0)

Open in new window

al4629740Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
FaustulusConnect With a Mentor Commented:
This code would convert what you already have into a loop that runs 3 times:
    Const AllMonths As String = "January February March"
    Dim i As Integer
    
    For i = 0 To 2
        select count(*) from table where Month = Split(AllMonths)(i)
        rec2.CursorLocation = adUseClient
        rec2.Open (esql), conn, adOpenStatic, adLockOptimistic

        Total = Total + rec.Fields(0)
    Next i

Open in new window

0
 
aikimarkCommented:
Does your table have a column named "month"?  If this is a new table, I should advise you to have column names that are different than database function names.

What type of column is "month" (numeric or text)?

Normally, I would create a Select statement that summed/counted the data for a date range, rather than executing three different Select statements.
Example:
select count(*) from table where Month Between 1 and 3

Open in new window

0
 
al4629740Author Commented:
Month column is text.

What I need was a loop to go through specified months as mentioned in the question.  

Thanks
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
GrahamSkanConnect With a Mentor RetiredCommented:
This would answer one interpretation of your question:
Sub LoopSub(conn As Connection)
    Dim Var1 As String
    Dim m As Integer
    Dim eSQL As String
    Dim rec2 As New ADODB.Recordset
    Dim Total As Double
    
    For m = 1 To 3
        Var1 = Format(DateSerial(2000, m, 1), "MMMM")
        eSQL = "select count(*) from table where Month = " ' & Var1 & '"
        rec2.CursorLocation = adUseClient
        rec2.Open eSQL, conn, adOpenStatic, adLockOptimistic
        Total = Total + rec2.Fields(0)
        rec2.Close
    Next m
End Sub

Open in new window

0
 
shorvathCommented:
select count(*)
from table
where Month = 'January'
or  Month = 'February'
or Month = ' March'
0
 
shorvathCommented:
No loop required, just the query
0
 
al4629740Author Commented:
Thanks for the effort
0
All Courses

From novice to tech pro — start learning today.