al4629740
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.
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?
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
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
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
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?
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.
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.
ASKER
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
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
ASKER
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.
ASKER
Martin,
Almost there. The output is only commas (,,,,) It doesn't show the numbers
Almost there. The output is only commas (,,,,) It doesn't show the numbers
ASKER
Should there be quotes around the numbers for code = "1"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is the recordset that is pulled up. You will see the highlighted yellow are the records in question.
output.xlsx
output.xlsx
ASKER
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
ASKER
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.
ASKER
Ahhhhhh. They don't match! This recordset has numericals counted. UGH!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
tblOrgActivities.xlsx
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
tblOrgHours.xlsxtblOrgActivities.xlsx
Sorry but without the Access database there's not much more I can do.
ASKER
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.
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.
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" ;-).
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.
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.
ASKER
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.
Thanks for all the help Martin on this one also.
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.