Use Option Group to Filter Combo Box Values

[dgj32784]

I have five options in an option group: [A-E], [F-J], [K-O], [P-T], [U-Z]. These "ranges" represent alphabetical ranges that I would like combo box values to be "filtered" on. The combo box is a listing of counties that occur within the state. If the user hits the option [A-E], I'd like the combo box to only display counties that begin with a letter between A and E. How do I accomplish this with VBA? Is this a select case scenario? It would be easy to get the first letter of the county using Left, I just don't know how to take that value and have it "filter" the combo box choices.

Any pointers would be appreciated.

Thanks,
David

 

[Beetle]

Assuming the actual values of the choices in your option group are 1 through 5, you would use code like the following in the After Update event of the option group;

Private Sub YourOptionGroup_AfterUpdate()
 
Dim strCriteria As String
Dim strSQL As String
 
Select Case Me!YourOptionGroup
    Case 1
        strCriteria = "'A','B','C','D','E'"
    Case 2
        strCriteria = "'F','G','H','I','J'"
    Case 3
        strCriteria = "'K','L','M','N','O'"
    Case 4
        strCriteria = "'P','Q','R','S','T'"
    Case 5
        strCriteria = "'U','V','W','X','Y','Z'"
End Select
 
strSQL = "Select [YourField] From YourTable Where Left([YourField],1) In(" & strCriteria & ");"
 
Me!YourCombo.RowSource = strSQL
 
End Sub

 

[dgj32784]

Thanks Sean! This worked like a charm. Is there a way to refer to the letters as a range, instead of listing them individually. The code you provided works

strCriteria = "'A','B','C','D','E'"

, but I may want to change the range in the future. I tried

 strCriteria = "'[A-E]'"

and a couple other things, but could not get any to work. I'm an Access, VBA, and SQL novice.

Thanks again,
David

 

[dgj32784]

Please disregard my previous post. I figured out the syntax for the range. Code below for the record...

Private Sub grpCountyListCategories_AfterUpdate()
 
    Dim strCriteria As String
    Dim strSQL As String
 
    'code to narrow the county option group values by first letter
    Select Case Me!grpCountyListCategories
        Case 1
            'code to define variable value between A - E
            strCriteria = "'[A-E]'&'*'"
        Case 2
            'code to define variable value between F - J
            strCriteria = "'[F-J]'&'*'"
        Case 3
            'code to define variable value between K - O
            strCriteria = "'[K-O]'&'*'"
        Case 4
            'code to define variable value between P - T
            strCriteria = "'[P-T]'&'*'"
        Case 5
            'code to define variable value between U - Z
            strCriteria = "'[U-Z]'&'*'"
        Case 6
            'code to define variable value between A -Z
            'sets combo box values back to full list
            strCriteria = "'[A-Z]'&'*'"
    End Select
 
    'SQL statement to select values from the query that fall within the ranges specified in option group cases listed above
    strSQL = "Select strCountyName From qryNorthCarolinaCounties Where Left ([strCountyName],1) Like(" & strCriteria & ");"
 
    'Sets combo box row source equal to SQL select statement
    Me!cboCountyName.RowSource = strSQL
 
End Sub