Filter records greater than

correlate
correlate used Ask the Experts™
on
Dear Experts

I am using Access 2003 and have a simple form that filters records (the code is attached).  Currently the filters are all based on Text box values.  

I would like to change one of these to apply a filter to the number of years experience a person may have (for example to anyone with over, say 5 years experience)

The code I have adapted from the text filters is this but cant work out how to change it to filter as a greater than

If (Me![ExperienceYrsCombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[Yrsexperience] LIKE '*" & Me!ExperienceYrsCombo.Value & "*' AND "

The <Any> value is set as a default in each of the Combo boxes

Hopefully this makes sense &. Can anybody help?

Private Sub ExperienceYrsCombo_AfterUpdate()
    Call UpdateFilter
End Sub
 
Private Sub IndustryCombo_AfterUpdate()
    Call UpdateFilter
End Sub
Private Sub LocationCombo_AfterUpdate()
    Call UpdateFilter
End Sub
Private Sub EmployerCombo_AfterUpdate()
    Call UpdateFilter
End Sub
Private Sub JobCombo_AfterUpdate()
    Call UpdateFilter
End Sub
Private Sub LanguageCombo_AfterUpdate()
    Call UpdateFilter
End Sub
 
Private Sub MasterSectorCombo_AfterUpdate()
    Call UpdateFilter
End Sub
 
 
Private Sub Open_PopUp_Menu_Click()
On Error GoTo Err_Command57_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Frm-Menu-Pop-Up"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Command57_Click:
    Exit Sub
 
Err_Command57_Click:
    MsgBox Err.description
    Resume Exit_Command57_Click
    
End Sub
 
 
Private Sub QuickViewCMD_Click()
On Error GoTo Err_Command59_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frm_quick_view"
    
    stLinkCriteria = "[people_id]=" & Me![people_id]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Command59_Click:
    Exit Sub
 
Err_Command59_Click:
    MsgBox Err.description
    Resume Exit_Command59_Click
    End Sub
 
Private Sub ResetCmd_Click()
    Call UpdateComboBoxes(True)
    Me!LocationCombo = "<Any>"
    Me!JobCombo = "<Any>"
    Me!Employercombo = "<Any>"
    Me!IndustryCombo = "<Any>"
    Me!LanguageCombo = "<Any>"
    Me!MasterSectorCombo = "<Any>"
    Me!SectorCombo = "<Any>"
    Me!ExperienceYrsCombo = "<Any>"
    Me.FilterOn = False
End Sub
 
Private Sub Form_Load()
   Call UpdateComboBoxes(True)
End Sub
Private Sub UpdateFilter()
Dim filterstring As String
filterstring = ""
If (Me![LocationCombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[CurrentLoc] = '" & Me!LocationCombo.Value & "' AND "
If (Me![JobCombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[jobfunction] = '" & Me!JobCombo.Value & "' AND "
If (Me![Employercombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[Firm] = '" & Me!Employercombo.Value & "' AND "
If (Me![IndustryCombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[industry] = '" & Me!IndustryCombo.Value & "' AND "
If (Me![LanguageCombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[Language] LIKE '*" & Me!LanguageCombo.Value & "*' AND "
If (Me![MasterSectorCombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[Master_Sector] LIKE '*" & Me!MasterSectorCombo.Value & "*' AND "
If (Me![SectorCombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[Sector] LIKE '*" & Me!SectorCombo.Value & "*' AND "
If (Me![ExperienceYrsCombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[Yrsexperience] > '*" & Me!ExperienceYrsCombo.Value & "*' AND "
If Len(filterstring) > 0 Then
    filterstring = Left$(filterstring, Len(filterstring) - 5)
    Me.Filter = filterstring
    Me.FilterOn = True
    Call UpdateComboBoxes(filterstring)
Else
    Me.FilterOn = False
    Call UpdateComboBoxes(True)
End If
    Exit Sub
End Sub
Private Sub UpdateComboBoxes(myFilter As Variant)
 
Me!LocationCombo.RowSource = "SELECT [CurrentLoc] FROM Qry_People_Quick_Search WHERE " & myFilter & "  UNION SELECT '<Any>' FROM Qry_People_Quick_Search ORDER BY Qry_People_Quick_Search.[CurrentLoc]"
Me!JobCombo.RowSource = "SELECT [jobfunction] FROM Qry_People_Quick_Search WHERE " & myFilter & " UNION SELECT '<Any>' FROM Qry_People_Quick_Search ORDER BY Qry_People_Quick_Search.[jobfunction]"
Me!Employercombo.RowSource = "SELECT [Firm] FROM Qry_People_Quick_Search WHERE " & myFilter & " UNION SELECT '<Any>' FROM Qry_People_Quick_Search ORDER BY Qry_People_Quick_Search.[Firm]"
Me!IndustryCombo.RowSource = "SELECT [industry] FROM Qry_People_Quick_Search WHERE " & myFilter & " UNION SELECT '<Any>' FROM Qry_People_Quick_Search ORDER BY Qry_People_Quick_Search.[industry]"
Me.Requery
End Sub
 
Private Sub SectorCombo_AfterUpdate()
    Call UpdateFilter
End Sub
Private Sub Lookup_Click()
On Error GoTo Err_Lookup_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Frm_People"
    
    stLinkCriteria = "[people_id]=" & Me![people_id]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Lookup_Click:
    Exit Sub
 
Err_Lookup_Click:
    MsgBox Err.description
    Resume Exit_Lookup_Click
    
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Testing for something to be greater than * xxx* dooesn't make sense I'm afraid.
Why do you have to use the * anyway? Surely a YearsExperience field can only contain a number?


Author

Commented:
It would be testing for a number.
The * is a legacy from when searching within a text field (as the field could contain more characters than the selection from the combo, i.e the combo looks up "apple" from the sentence "Peter likes apples and pears")
Then it should be..

If (Me![ExperienceYrsCombo].Value & vbNullString) <> "<Any>" Then filterstring = filterstring & "[Yrsexperience] >" &  Me!ExperienceYrsCombo.Value & " AND "

Author

Commented:
Brilliant thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial