Link to home
Start Free TrialLog in
Avatar of al4629740
al4629740Flag for United States of America

asked on

Proper use of Case Statement in vb6

I have the following table.  Based upon the records I pull I need to compare them to this table and then build up my variable (SectorString) accordingly.   I'm thinking that a case statement would work best.


    Sectors	
1	Business
2	Civic/Volunteer
3	Community Residents
4	Schools
5	Faith-based Organizations
6	Local Government
7	Healthcare
8	Law Enforcement
9	Media
10	Parents/Guardians of middle schools students, high schools students and alternative high school students
11	Philanthropic
12	Human Support  Agencies
13	Youth

Open in new window


Here is the code I came up with but it does not seem to be working. Can you notice what is wrong with my case statement?

 SectorString = ""
                
                For i = 26 To 38
                
                   Select Case rec.Fields(i)
                        Case "Business"
                            Code = 1
                        Case "Civic-Volunteer"
                            Code = 2
                        Case "Community Resident"
                            Code = 3
                        Case "Schools"
                            Code = 4
                        Case "Faith Based"
                            Code = 5
                        Case "Local Government"
                            Code = 6
                        Case "Healthcare"
                            Code = 7
                        Case "Law Enforcement"
                            Code = 8
                        Case "Media"
                            Code = 9
                        Case " Parent or Guardian"
                            Code = 10
                        Case "Philanthropic"
                            Code = 11
                        Case "Human Support Agencies"
                            Code = 12
                        Case "Youth"
                            Code = 13
                        SectorString = SectorString & Code & ","
                    End Select
                Next i

Open in new window

Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

What is 26 to 38 is not clear. Please clarify. I can use my imagination but would prefer to stick to reality instead.

Furthermore, what is the frequency of addition / modification / deletion of these values in terms of days / months / years, etc.

Lately, I have primarily been coding in C#, so could you please remind if the line - SectorString = SectorString & Code & "," - executes just in one case or all. All - highly unlikely. Is this expected to be outside of Select Case .... End Select?

Also, better give some background as to on the basis of which set of values, which string intent to build.
Avatar of al4629740

ASKER

I'll try to answer your questions as best I can.

26 to 38 are the references to the recordset.  You can see from the code rec.Fields(i) is referencing a set that I pulled up earlier in the code.

I don't understand the frequency question and as such it may not apply here.

I don't usually use case statements so I will need an expert to show me if I'm utilizing the case statement correctly.

I need to build the SectorString value
At line 4, add this
msgbox rec.Fields(I)

Does it show you what you are expecting?
Let me try it otherway round. The questions were asked to understand the ultimate goal and resolve it in the best possible way irrespective of usage of select case statement in VB6. Please do let me know if it is okay to take that path. Or you have already decided on the path.

Frequency question was from code maintainance point of view. Very important from long run point of view.

Okay....that's fine. However, is above code giving you expected output already. If so, we may not look into other aspects of it.

Slightly off the topic, I often try to see patterns and resolve then in quickest possible manner. For example, assignment of code can be outside of the case statement, simply being code = i - 25.
Martin,

After looking at the msgbox output, I realized what I was doing and asking was wrong.  Here is what I am trying to do:

When the For statement runs (For i = 26 to 38)  It runs through all the recordset totals for business, schools, media, etc...     As it does that, I want to check each record to see if it is greater than 0 first.  If it is greater than 0, then I want to add the corresponding case statement value into the SectorString.  

For example, if there is a value greater than 0 in Media, Law Enforcement and Youth, then the SectorString should show 8,9,13
Perhaps I complicated this by choosing a case statement, but any advice would be appreciated.
Perhaps I complicated this by choosing a case statement, but any advice would be appreciated.

If this is in response to my comment, I had rather have it sorted out at the back-end level itself in the query. Assuming everything is coming from back-end in first place.

Which database is this by the way. I could be of help if MS SQL Server.

JUST TO BE SURE....only if you are open for it.
Move line 32 to after line 33.
Martin,

Almost there.  The output is only commas (,,,,)  It doesn't show the numbers
Should there be quotes around the numbers for code = "1"
SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here is the recordset that is pulled up.  You will see the highlighted yellow are the records in question.
output.xlsx
Here is what I have currently.  I made an adjustment to remove out the records that = 0

SectorString = ""
                
                For i = 26 To 38
                    If rec.Fields(i) > 0 Then
                   Select Case rec.Fields(i)
                        Case "Business"
                            Code = "1"
                        Case "Civic-Volunteer"
                            Code = "2"
                        Case "Community Resident"
                            Code = "3"
                        Case "Schools"
                            Code = "4"
                        Case "Faith Based"
                            Code = "5"
                        Case "Local Government"
                            Code = "6"
                        Case "Healthcare"
                            Code = "7"
                        Case "Law Enforcement"
                            Code = "8"
                        Case "Media"
                            Code = "9"
                        Case " Parent or Guardian"
                            Code = "10"
                        Case "Philanthropic"
                            Code = "11"
                        Case "Human Support Agencies"
                            Code = "12"
                        Case "Youth"
                            Code = "13"
                    End Select
                    SectorString = SectorString & Code & ","
                    End If
                    
                Next i

Open in new window

My output from the case statement is all commas.  That means that something is passing through the case statement, but the value (Code) gets lost and left with commas.
Ahhhhhh.  They don't match!  This recordset has numericals counted.  UGH!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That will be slower than

For i = 26 To 38
    Select Case True
        Case rec![Business] > 0
            Code = "1"
        Case rec![Civic-Volunteer] > 0
            Code = "2"
        ' And the other cases
    End Select
    ' This elininates the trailing comma
    If SectorString <> "" Then
        SectorString = SectorString & "," & Code
    Else
        ' It's the first one
        SectorString = Code
    End If
Next

Open in new window

Thanks Martin,

When I use this code, all the output turns out to be  1,1,1,1,1,1,1,1,1,1,1,1,1  which can't be right.  Any ideas?


SectorString = ""
                
                For i = 26 To 38
                    Select Case True
                        Case rec![Business] > 0
                            Code = "1"
                        Case rec![Civic-Volunteer] > 0
                            Code = "2"
                        Case rec![Community Resident] > 0
                            Code = "3"
                        Case rec![Schools] > 0
                            Code = "4"
                        Case rec![Faith Based] > 0
                            Code = "5"
                        Case rec![Local Government] > 0
                            Code = "6"
                        Case rec![Healthcare] > 0
                            Code = "7"
                        Case rec![Law Enforcement] > 0
                            Code = "8"
                        Case rec![Media] > 0
                            Code = "9"
                        Case rec![ Parent or Guardian] > 0
                            Code = "10"
                        Case rec![Philanthropic] > 0
                            Code = "11"
                        Case rec![Human Support Agencies] > 0
                            Code = "12"
                        Case rec![Youth] > 0
                            Code = "13"
                    End Select
                    ' This elininates the trailing comma
                    If SectorString <> "" Then
                        SectorString = SectorString & "," & Code
                    Else
                        ' It's the first one
                        SectorString = Code
                    End If
                Next

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can you supply the complete project in a zip file, including the source database? If the source data is sensitive, please create a small database with dummy data.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
would that make it better in terms of performance?
Yes it would because with the code you just posted, all 13 Ifs are always evaluated even if the first one is true, whereas with a Select Case the Cases are examined top to bottom and as soon as one one is True the rest are ignored.
The project is very large as it has been built over many years.  I think it might be feasible to give you the module though.

Dim rec1 As ADODB.Recordset
Dim ApExcel As Object 'To open Excel
Dim Sheet As String
Dim num(1 To 40) As Double
    Dim LastName As String
    Dim FirstName As String
Dim First(1 To 18) As String
Dim Second(1 To 18) As String
Dim Third(1 To 18) As Integer
Dim ServerTime As Date
Dim MinimumDate As Date
Dim MaximumDate As Date
Dim SectorString As String
Dim c As Integer

    
Private Sub Command1_Click()

   





Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
Set rec1 = New ADODB.Recordset
conn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",xxx;Network Library=DBMSSOCN;Initial Catalog= " & CAPDB & "; User ID=xxPassword=xxxx"

      

esql = ";With CTE_Hours as (select distinct AgencyID, Agency,Classification,Objectives, Deliverables, Advocate, AdvocacyType, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate, Fiscal From tblOrgHours Where [Hours] > 0) select H.Agency,A.ActivityName,H.Classification,H.ActivityDate,H.Objectives,H.Deliverables,H.Advocate,H.AdvocacyType,Count(H.RegID) as [# individuals]" & _
       ",SUM(CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]" & _
       ",SUM(CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]" & _
       ",SUM(CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then 1 else 0 end) as [Ages 19-24]" & _
       ",SUM(CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then 1 else 0 end) as [Ages 25-65]" & _
       ",SUM(CASE when R.AgeCurrent >= 66 then 1 else 0 end) as [Ages 65+]" & _
       ",SUM(CASE when R.Board = 1 then 1 else 0 end) as [CommunityCommittee],SUM(CASE when R.YouthCommittee = 1 then 1 else 0 end) as [YouthCommittee],SUM(CASE when R.Parentcheck = 1 then 1 else 0 end) as [Parentcheck],SUM(CASE when R.CommunityResident = 1 then 1 else 0 end) as [CommunityResident]" & _
       ",SUM(CASE when R.Race = 'Asian' then 1 else 0 end) as [Asian]" & _
       ",SUM(CASE when R.Race = 'African-American' then 1 else 0 end) as [African-American]" & _
       ",SUM(CASE when R.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]" & _
       ",SUM(CASE when R.Race = 'Native-American' then 1 else 0 end) as [Native-American]" & _
       ",SUM(CASE when R.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]" & _
       ",SUM(CASE when R.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]" & _
       ",SUM(CASE when R.Gender = 'Male' then 1 else 0 end) as [Male]" & _
       ",SUM(CASE when R.Gender = 'Female' then 1 else 0 end) as [Female]" & _
       ",SUM(CASE when R.Sector = 'Business' then 1 else 0 end) as [Business]" & _
       ",SUM(CASE when R.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer]" & _
       ",SUM(CASE when R.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident]" & _
       ",SUM(CASE when R.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based]" & _
       ",SUM(CASE when R.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare]" & _
       ",SUM(CASE when R.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies]" & _
       ",SUM(CASE when R.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement]" & _
       ",SUM(CASE when R.Sector = 'Local Government' then 1 else 0 end) as [Local Government]" & _
       ",SUM(CASE when R.Sector = 'Media' then 1 else 0 end) as [Media],SUM(CASE when R.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian],SUM(CASE when R.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic],SUM(CASE when R.Sector = 'Schools' then 1 else 0 end) as [Schools],SUM(CASE when R.Sector = 'Youth' then 1 else 0 end) as [Youth] from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID inner join tblOrgActivities A on H.ActivityID = A.ActivityID where R.AgeCurrent between 11 and 999 And h.Agency = '" & Combo12.Text & "' And H.Fiscal = 2018 And H.ActivityDate >= '" & DTPicker1 & "' And H.ActivityDate < '" & DTPicker2 & "' group by H.Agency, H.Classification,A.ActivityName,H.ActivityDate, H.Objectives, H.Deliverables, H.Advocate, H.AdvocacyType Order by H.Agency, H.Classification,H.ActivityDate,A.ActivityName"


If rec.State = adStateOpen Then
        rec.Close
End If

      rec.CursorType = adOpenStatic
      rec.CursorLocation = adUseClient
      rec.LockType = adLockOptimistic
      rec.Open esql, conn, , , adCmdText

Text1 = rec.RecordCount

If rec.EOF And rec.BOF Then
    Set DataGrid1.DataSource = Nothing
    MsgBox "There are no Records for this Query. ", vbOKOnly, "No Data Found"
    Exit Sub
    
End If

      
        Set DataGrid1.DataSource = rec
        
        
    rec.MoveFirst
    

End Sub

Private Sub Command2_Click()



Set rec1 = New ADODB.Recordset
esql1 = "select GetDate()"
                rec1.Open (esql1), conn, adOpenStatic, adLockReadOnly
                ServerTime = rec1.Fields(0)
                rec1.Close
                
                
If rec.State = adStateOpen Then
    If DataGrid1.ApproxCount > 0 Then


    If DataGrid1.ApproxCount = 0 Then
    MsgBox "You need to pull up records in order to Print.", vbOKOnly, "No records found"
    Exit Sub
    End If
    
    
'On Error GoTo CheckError1

   
Set ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel


'On Error GoTo CheckError2


ApExcel.Workbooks.Open "http://www.xxxx.org/ReviewGrid.xls"



'--------------------------------
    ApExcel.Workbooks("ReviewGrid.xls").Sheets("Sheet1").Cells(1, 1).Formula = Combo12.Text
    ApExcel.Workbooks("ReviewGrid.xls").Sheets("Sheet1").Cells(3, 1).Formula = "Attendance Dates: " & DTPicker1 & " - " & DTPicker2
'--------------------------------






ApExcel.Workbooks("ReviewGrid.xls").Sheets("Sheet1").Cells(4, 1).Formula = "TimeStamp: " & ServerTime
    
    
    
rec.MoveFirst
i = 6

'--------------------------------
    For k = 1 To 29
        ApExcel.Workbooks("ReviewGrid.xls").Sheets("Sheet1").Cells(i, k).Formula = rec.Fields(k).Name
    Next k
'--------------------------------





Do Until rec.EOF

  
        
                 
 i = i + 1
                
                
'--------------------------------
                    For k = 1 To 29
                    ApExcel.Workbooks("ReviewGrid.xls").Sheets("Sheet1").Cells(i, k).Formula = rec.Fields(k)
                    Next k
'--------------------------------






rec.MoveNext
Loop
                    
 
    
ApExcel.Workbooks("ReviewGrid.xls").Sheets("Sheet1").Columns("A:W").EntireColumn.AutoFit

Exit Sub

Else
MsgBox "Please View Results before importing data to Excel", vbOKOnly, "No Data"
End If
Else
MsgBox "Please View Results before importing data to Excel", vbOKOnly, "No Data"
End If

Exit Sub
CheckError1:
    MsgBox "You will need to install Microsoft Excel on this machine in order to print an Excel Copy", vbOKOnly, "Excel Not Installed"
Exit Sub

CheckError2:
MsgBox "There was an error in your data.  Please report it to CAP at 312-588-3828.  Your work should be saved, but please reopen the program after it closes.", vbOKOnly, "Restart Program"
End

End Sub

Private Sub Command3_Click()

Dim Form As Form
   For Each Form In Forms
      Unload Form
      Set Form = Nothing
   Next Form
End

End Sub




Private Sub Command4_Click()



Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
Set rec1 = New ADODB.Recordset
conn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",xxx;Network Library=DBMSSOCN;Initial Catalog= " & CAPDB & "; User ID=xxPassword=xxxx"



Set rec1 = New ADODB.Recordset
esql1 = "select GetDate()"
                rec1.Open (esql1), conn, adOpenStatic, adLockReadOnly
                ServerTime = rec1.Fields(0)
                rec1.Close
                
                

    
    
'On Error GoTo CheckError1

   
Set ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel


'On Error GoTo CheckError2


ApExcel.Workbooks.Open ("http://www.xxxx.org/CYSReport.xls")



'Dumping into Cover Page-----------------------------------------------------------------------------------

'Committee Information-------------------------------------------

    esql = "select [System Name], Address, City, Zip, [ServiceArea], Percentage, Phone, Fax, Email from tblOrgProfile where [System Name] = '" & Combo12 & "'"
    
     
     If rec.State = adStateOpen Then
            rec.Close
    End If
    
          rec.CursorType = adOpenStatic
          rec.CursorLocation = adUseClient
          rec.LockType = adLockOptimistic
          rec.Open esql, conn, , , adCmdText
    
            
            
        rec.MoveFirst
        
        
          
    
    
    
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(12, 2).Formula = rec![System Name]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(13, 2).Formula = rec!Address & "," & rec!City & "," & rec!ZIP
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(14, 2).Formula = rec![ServiceArea]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(15, 2).Formula = rec![Percentage]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(18, 2).Formula = rec![Phone]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(19, 2).Formula = rec![Fax]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(20, 2).Formula = rec![Email]
    
    


'Meetings-----------------------------------
c = 1

For q = 1 To 4
        c = c + 1
        
    If q = 1 Then
    QuarterA = "07/01/2017"
    QuarterB = "09/30/2017"
    End If
    
    If q = 2 Then
    QuarterA = "10/01/2017"
    QuarterB = "12/31/2017"
    End If
    
    If q = 3 Then
    QuarterA = "01/01/2018"
    QuarterB = "03/31/2018"
    End If
    
    If q = 4 Then
    QuarterA = "04/01/2018"
    QuarterB = "06/30/2018"
    End If
        
    esql = ";With CTE_Hours as(select distinct  ActivityDate, Classification, H.ActivityID, ActivityName from tblOrgHours h inner join tblOrgActivities A on H.ActivityID = A.ActivityID Where [Hours] > 0 And H.Agency = 'Administrator' And H.Fiscal = 2018 And H.ActivityDate >= '" & QuarterA & "' And H.ActivityDate < '" & QuarterB & "')" & _
     "select SUM(CASE when H.Classification = 'Organizing' then 1 else 0 end) as [Organizing] ,SUM(CASE when H.Classification = 'Direct Service' then 1 else 0 end) as [Direct Service] ,SUM(CASE when H.Classification = 'Advocacy' then 1 else 0 end) as [Advocacy] ,SUM(CASE when ActivityName = 'Community Committee' then 1 else 0 end) as [Community Committee],SUM(CASE when ActivityName = 'Youth Committee' then 1 else 0 end) as [Youth Committee]from CTE_Hours H"
     
     
     If rec.State = adStateOpen Then
            rec.Close
    End If
    
          rec.CursorType = adOpenStatic
          rec.CursorLocation = adUseClient
          rec.LockType = adLockOptimistic
          rec.Open esql, conn, , , adCmdText
    
            
            
        rec.MoveFirst
        
        
        
    
    
    
    
    
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(23, c).Formula = rec![Organizing]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(24, c).Formula = rec![Direct Service]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(25, c).Formula = rec![Advocacy]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(29, c).Formula = rec![Community Committee]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(30, c).Formula = rec![Youth Committee]
    
    
    
    'Demographics-----------------------------------
    
    esql = ";With CTE_Hours as(select distinct  RegID from tblOrgHours h inner join tblOrgActivities A on H.ActivityID = A.ActivityID Where [Hours] > 0 And h.Agency = '" & Combo12 & "' And H.Fiscal = " & Fiscal & " And H.ActivityDate >= '" & QuarterA & "' And H.ActivityDate < '" & QuarterB & "')" & _
           "select Count(DISTINCT CASE when R.Race = 'Asian' then h.regid else null end) as [Asian],Count(DISTINCT CASE when R.Race = 'African-American' then h.regid else null end) as [African-American] ,Count(DISTINCT CASE when R.Race = 'Caucasian' then h.regid else null end) as [Caucasian] ,Count(DISTINCT CASE when R.Race = 'Native-American' then h.regid else null end) as [Native-American] ,Count(DISTINCT CASE when R.Race = 'Multi-Racial' then h.regid else null end) as [Multi-Racial]" & _
           ",Count(DISTINCT CASE when R.Race = 'Latino-Hispanic' then h.regid else null end) as [Latino-Hispanic] ,Count(DISTINCT CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then h.regid else null end) as [Ages 11-13],Count(DISTINCT CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then h.regid else null end) as [Ages 14-18] ,Count(DISTINCT CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then h.regid else null end) as [Ages 19-24] ,Count(DISTINCT CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then h.regid else null end) as [Ages 25-65]" & _
           ",Count(DISTINCT CASE when R.AgeCurrent >= 66 then h.regid else null end) as [Ages 65+],Count(distinct CASE when R.Gender = 'Male' then h.regid else null end) as [Male]" & _
           ",Count(distinct CASE when R.Gender = 'Female' then h.regid else null end) as [Female] from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID where R.AgeCurrent between 11 and 999"
     
     If rec.State = adStateOpen Then
            rec.Close
    End If
    
          rec.CursorType = adOpenStatic
          rec.CursorLocation = adUseClient
          rec.LockType = adLockOptimistic
          rec.Open esql, conn, , , adCmdText
    
            
            
        rec.MoveFirst
        
        
        
    
    
    
    
    
         ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(33, c).Formula = rec![Asian]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(34, c).Formula = rec![African-American]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(35, c).Formula = rec![Native-American]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(36, c).Formula = rec![Caucasian]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(37, c).Formula = rec![Multi-Racial]
    
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(40, c).Formula = rec![Latino-Hispanic]
    
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(42, c).Formula = rec![Ages 11-13]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(43, c).Formula = rec![Ages 14-18]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(44, c).Formula = rec![Ages 19-24]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(45, c).Formula = rec![Ages 25-65]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(46, c).Formula = rec![Ages 65+]
        
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(49, c).Formula = rec![Female]
        ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(50, c).Formula = rec![Male]
    
    
Next q


'Organizing---------------------------------------------------------------------------


esql = ";With CTE_Hours as (select distinct AgencyID, Agency,Classification,Objectives, Deliverables, Advocate, AdvocacyType, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate, Fiscal From tblOrgHours Where [Hours] > 0 And Classification = 'Organizing') select H.Agency,A.ActivityName,H.Classification,H.ActivityDate,H.Objectives,H.Deliverables,H.Advocate,H.AdvocacyType,Count(H.RegID) as [# individuals]" & _
       ",SUM(CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]" & _
       ",SUM(CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]" & _
       ",SUM(CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 the

Open in new window

tblOrgHours.xlsx
tblOrgActivities.xlsx
Sorry but without the Access database there's not much more I can do.
Its ok, I have a working solution for now.
i don't agree to the way you closed the question. Martin's suggestion #a42477366 is wrong, as you confirmed yourself. It doesn't matter whether the IF cascade is slower than the CASE, because CASE cannot do what you are after. You need to go thru all checks for each record, as you want to combine results.
And more, Martin should have been aware of that the FOR loop doesn't make sense either anymore, as there is no index to use..
You were on the right track in #a42477353, all you needed from there was confirmation and clarification, as done in my comment.

In short, I think you should accept my comment and your own #a42477353.
Qlemo, first let me say that i don't have any problem with the points being redistributed, and that's because it's been a very long time since I did any coding agains't databases and so I don't know for sure if you are right or wrong. Having said that, take a look at these pictures. They are from an old VB6 demonstration project of mine which searches for Products based on user input in the Northwind database and displays them in a Listview control. For the purposes of this discussion I added the "verification" block of code which crudely simulates what I believe the Asker is trying to do by examining Fields 2 and 3 of the dataset looking for values of 4 and 9. The picture is the result of entering 'f' in the search box and clicking the 'Find it' button. I may not understand your comment but it looks to me like my code is doing what the Asker wants.
User generated imageUser generated image
Martin, the difference in that last comment is that you are going thru the field numbers with the FOR, which has been the original approach of the OP. That much fits together.
The original code would work the same, if the case would have checked against the field name (probably investigatable with rs!Field(i).Name), and also included a >0 check of the value of the field.
But then again, the CASE is processed n times for each row - it needs to be checked for each field in a loop. Performancewise there is no gain. You either have n CASE with n cases to check in a loop, or n IFs written out and no loop. Unless I would need a flexible solution, e.g. with field names and codes stored in a config table, I would use the direct IF approach.

Your suggested code in #42477366 uses a FOR loop without using the FOR variable anywhere - that is always "suspicious" ;-).
You are correct. I was thinking (or not thinking) that the rec![whatever] would resolve to 1 or 2, etc.  

The Asker has reported the thread mentioning that he still wants to split the points, but as I said, if you object to that, no problem.
At the end of the day, Qlemo is right.  I did end up going with the solution that I indicated above and I did consider his statement in doing so since nothing else was working.  And judging by the follow up discussion, the If/Then statement was the practical way to go.  I will reallocate the points accordingly.

Thanks for all the help Martin on this one also.