Solved

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

Posted on 2013-01-06
3
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

730 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