Build Filter using 2 columns of Multi-Select List Box

[kristi1023]

I am using a Multi-Select List box that searches Skills and it works great. However, I now have to add Skill Level to the list box and perform the search based on 2 columns (Skill and Skill Level ID). Here is my code:

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varSkill As Variant
Dim varItem As Variant
Dim intIndex As Integer
Dim varSkillLevel As Variant

varWhere = Null ' Main filter
varSkill = Null ' Subfilter used for Skills
varSkillLevel = Null

' Check for Skills in multiselect list
For Each varItem In Me.lstSkills.ItemsSelected
varSkill = varSkill & "[Skill] = """ & _
Me.lstSkills.ItemData(varItem) & """ OR "
varSkillLevel = varSkillLevel & """" & Me.lstSkills.Column(2, varItem) & """, "
[highlight #FF99FF]Here I am checking for Skill Level ID (Column2)[/highlight]

Next

' Test to see if we have subfilter for Skills...
If IsNull(varSkill) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varSkill, 4) = " OR " Then
varSkill = Left(varSkill, Len(varSkill) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varSkill & " )"
[highlight #FF99FF]I think I need to build the Skill Level ID into the filter here? Any ideas would be great![/highlight]
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

Thanks..Kristi..

 

[ItIsHardToProgram]

Hi Kristi,

I would love to help you. First off what exactly does VarskillLvl returns, is there more then one level? 2ndly I might be wrong but your last strip of code removes ANDS but they are never added?... so you want to add skill lvls with AND and can't think of a way????

there is automaticaly a skill level with a skill right?

I might totaly be in the dark, but if I am right I may have an idea, otherwize alot of people are in better positions of helping you.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[kristi1023]

Hi IIHTP,

VarSkillLevel would return one skill level with each skill, i.e. Skill: C++ and SkillLevelID: 4(Skilled).

The code I posted is an excerpt, a subfilter is used elsewere that's why i'm stripping off the last and.

Yes there is a skill level with each skill; listbox looks like:

C++ Novice(SkillLevelID=1)
C++ User (SLID=2)

....

C++ Expert (SLID=5)
C# Novice
...

C# Expert


So, if I choose C++ Expert from listbox, I need to append C++ and 5 to my table. I have no problem appending the Skill, but I can't get the Skill and SkillLevelID to append correctly. Any ideas?

Thank you.

 

[ItIsHardToProgram]

What have you tried to append your skill level so far? You append them in 2 different fields?

Taking a look at skill and skillLevel, do they return the wanted values???

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[kristi1023]

I'm sorry I meant to say SEARCH rather than append I am thinking about 2 different problems at the same time. I need to search based upon the 2 columns in the multi-select listbox. In the example above, if I choose C++ and Expert, I need to search the table for skill AND skill level OR ...the second Skill AND skill level selected, etc.

 

[ItIsHardToProgram]

Right...

varSkillLevel = varSkillLevel & """" & Me.lstSkills.Column(2, varItem) & """, "

does not return the wanted values? corresponding the Varskill ?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[kristi1023]

Thanks for your help. I figured it out...

For Each varitem In Me.lstSkills.ItemsSelected
If varskill <> "" Then varskill = varskill & " OR "
varskill = varskill & "([Skill] = """ & Me.lstSkills.ItemData(varitem) & """ AND " & _
"[SkillLevelID]=" & Me.lstSkills.Column(2, varitem) & ")"
Next

 

[ItIsHardToProgram]

"[SkillLevelID]=" & Me.lstSkills.Column(2, varitem) & ")"

Works??? you don't need a space before the operator?

Glad you figured it out

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.