• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Access Vb - Filtering a List Control as Data is input in a Control Textbox - Grouping/Ordering List

Hello All

This is a followup question to Original Question on same topic

Irog assisted in fixing the problem after Mbiz had pointed me down the correct path but I was missing a piece still.

The filtering of the list as the user inputs info into a bound textbox control works but the list is sorting based on the ID number.  I need to modify the code to keep the list filtered by the StreetName (text).....

The original code that filters (unsorted) is.....

    If Len(StreetName.Text) > 0 Then
        strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets" & _
        " WHERE AddressStreetID > 2 AND StreetName LIKE '" & Me.StreetName.Text & "*'"
    Else
        strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets"
    End If

    Me.listExistingStreets.RowSource = strSQL

Open in new window


Now then I attempted to modify the strSQL to Group and Order the list but it makes the filtering of the list unresponsive....

The code I tried changing it to is:
    If Len(StreetName.Text) > 0 Then
        strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets" & _
        " WHERE AddressStreetID > 2 AND StreetName LIKE " & Me.StreetName & "*'" & _
        " GROUP BY AddressStreetID, StreetName" & _
        " ORDER BY StreetName"
    Else
        strSQL = "SELECT AddressStreetID, StreetName FROM dta_Streets" & _
        " GROUP BY AddressStreetID, StreetName" & _
        " ORDER BY StreetName"

    End If
    
    Me.listExistingStreets.RowSource = strSQL

Open in new window


Is my syntax wrong on adding the Group By and Order By ????
0
wlwebb
Asked:
wlwebb
  • 2
1 Solution
 
peter57rCommented:
" I need to modify the code to keep the list filtered by the StreetName (text)...."

Do you mean Sorted by streetname.  If so remove both Group By clauses (lines 4 and 8)
0
 
wlwebbAuthor Commented:
When I do that the filtering of the list doesn't work......  oops I did mean sorted by StreetName, you are correct.

PS Yes, StreetName is Text
0
 
wlwebbAuthor Commented:
Never mind... missed an ' in my code........  uggghhh
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now