Solved

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

Posted on 2013-01-06
3
299 Views
Last Modified: 2013-01-06
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
Comment
Question by:wlwebb
  • 2
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 38748180
" 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
 

Author Comment

by:wlwebb
ID: 38748184
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
 

Author Closing Comment

by:wlwebb
ID: 38748186
Never mind... missed an ' in my code........  uggghhh
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now