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

al4629740Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Nitin SontakkeDeveloperCommented:
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.
0
al4629740Author Commented:
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
0
Martin LissOlder than dirtCommented:
At line 4, add this
msgbox rec.Fields(I)

Does it show you what you are expecting?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Nitin SontakkeDeveloperCommented:
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.
0
al4629740Author Commented:
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
0
al4629740Author Commented:
Perhaps I complicated this by choosing a case statement, but any advice would be appreciated.
0
Nitin SontakkeDeveloperCommented:
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.
0
Martin LissOlder than dirtCommented:
Move line 32 to after line 33.
0
al4629740Author Commented:
Martin,

Almost there.  The output is only commas (,,,,)  It doesn't show the numbers
0
al4629740Author Commented:
Should there be quotes around the numbers for code = "1"
0
Martin LissOlder than dirtCommented:
That would seem to mean that the values in rec.Fields(I) is not matching any of the cases. If you think they should match, what does any one of them look like - exactly?
0
al4629740Author Commented:
Here is the recordset that is pulled up.  You will see the highlighted yellow are the records in question.
output.xlsx
0
al4629740Author Commented:
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

0
al4629740Author Commented:
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.
0
al4629740Author Commented:
Ahhhhhh.  They don't match!  This recordset has numericals counted.  UGH!
0
al4629740Author Commented:
I guess I'm better off putting  IF/THEN statements

If rec![Business] > 0 Then
 SectorString = SectorString & "1,"
End if
If rec![Civic-Volunteer] > 0 Then
 SectorString = SectorString & "2,"
End if
etc...
0
Martin LissOlder than dirtCommented:
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

0
al4629740Author Commented:
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

0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Now there is no relation to the record field number 26 to 38 anymore, you just repeat the same case 13 times, so generating the same value (nothing changes).
If you use rec![...], you need to check each field seperately in an IF instead of an CASE. If several checks have to be applied to the same data in a way 0 to n checks could succeed, CASE is wrong.

Remove the FOR. Then check each field as you have done with the last CASE but use IF.
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
Martin LissOlder than dirtCommented:
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.
0
al4629740Author Commented:
I'm a little behind the eight ball to get something working, so temporarily I put this together and its working.

My question is, if we could get a case statement working, would that make it better in terms of performance?  If so, I'll send over the module and some sample data.

Thanks for all your help!

SectorString = ""
                
                        If rec![Business] > 0 Then
                         SectorString = SectorString & "1,"
                        End If
                        If rec![Civic-Volunteer] > 0 Then
                         SectorString = SectorString & "2,"
                        End If
                        If rec![Community Resident] > 0 Then
                            SectorString = SectorString & "3,"
                        End If
                        If rec![Schools] > 0 Then
                            SectorString = SectorString & "4,"
                        End If
                        If rec![Faith Based] > 0 Then
                            SectorString = SectorString & "5,"
                        End If
                        If rec![Local Government] > 0 Then
                            SectorString = SectorString & "6,"
                        End If
                        If rec![Healthcare] > 0 Then
                            SectorString = SectorString & "7,"
                        End If
                        If rec![Law Enforcement] > 0 Then
                            SectorString = SectorString & "8,"
                        End If
                        If rec![Media] > 0 Then
                            SectorString = SectorString & "9,"
                        End If
                        If rec![Parent or Guardian] > 0 Then
                            SectorString = SectorString & "10,"
                        End If
                        If rec![Philanthropic] > 0 Then
                            SectorString = SectorString & "11,"
                        End If
                        If rec![Human Support Agencies] > 0 Then
                            SectorString = SectorString & "12,"
                        End If
                        If rec![Youth] > 0 Then
                            SectorString = SectorString & "13,"
                        End If

Open in new window

0
Martin LissOlder than dirtCommented:
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.
0
al4629740Author Commented:
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
0
Martin LissOlder than dirtCommented:
Sorry but without the Access database there's not much more I can do.
0
al4629740Author Commented:
Its ok, I have a working solution for now.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
0
Martin LissOlder than dirtCommented:
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.
V2.pngV1.png
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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" ;-).
0
Martin LissOlder than dirtCommented:
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.
0
al4629740Author Commented:
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.
0
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.