Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unanswered: Filter a textbox

    Hello.

    I have a text box that is based off of a query:

    SELECT DISTINCTROW [qryTelcos].[TELCO_HOTEL_ID],[qryTelcos].[OWNER_OPERATOR],[qryTelcos].[address_1], [qryTelcos].[CITY], [qryTelcos].[STATE], [qryTelcos].[country_name]FROM qryTelcos Order by [qryTelcos].[address_1]

    I want to be able to filter these records based on 3 different text boxes: txtOwner, txtCity and txtState.

    I've seen a number of solutions to achieve this, but what is one of the best way to do this using VBA?

    Thanks in advance,

    TS

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Use a WHERE clause ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    build the where clause depending on the content of your text boxes
    the follwoing should cater for the situation where one or more boxes may be filled

    eg
    strwhereclause = "Where "
    if not isnull(tbcontrol1) then strwhereclause=strwhereclause & "column1=" & tbcontrol1
    if not isnull(tbcontrol2) then
    if len(strwhereclause)>6 then strwhereclause=strwhereclause & " and "
    strwhereclause=strwhereclause & "column2=" & tbcontrol2
    endif
    if not isnull(tbcontrol3) then
    if len(strwhereclause)>6 then strwhereclause=strwhereclause & " and "
    strwhereclause=strwhereclause & "column3=" & tbcontrol3
    endif

    you can use if len(tbcontrol3) in place of not null if required

    if the value is string then you will need to enclose the tbcontrolx with sibgle or double quotes
    eg
    strwhereclause=strwhereclause & "column3=" & chr$(34) & tbcontrol3 & chr$(34)

    if you allow wild cards then you will need to modify the code so that it uses the like predicate

    eg
    select 'City from tblCities where City like "New*";'

    which will return cities starting with NEW eg "Newcastle upon Tyne, Newcastle under Lyme, Newark"
    OR
    select 'City from tblCities where City like "??nch*";'
    which will return cities starting with ??nch eg Manchester
    OR
    select 'City from tblCities where City like "*Chester*";'
    which will return cities containing chester eg Manchester, Chichester, Chester
    HTH

  4. #4
    Join Date
    Jan 2004
    Posts
    6

    Red face Filter Listbox

    Thanks for the help. Here is the code that I have come up with. I have this code in my AfterUPdate for txtOwner, txtCity and txtState:

    Private Sub txtCity_AfterUpdate()
    Dim strCriteria As String
    Dim blnMultiple As Boolean
    blnMultiple = False
    If Not (IsNull(txtCity.Value)) Or Not (IsNull(txtState.Value)) Or Not (IsNull(txtOwner)) Then
    strCriteria = "WHERE "
    End If
    If Not (strCriteria = "") Then
    If Not (IsNull(txtCity.Value)) Then
    strCriteria = strCriteria + "[CITY] like """ & txtCity.Value & "*" & """"
    blnMultiple = True
    End If
    If Not (IsNull(txtState.Value)) Then
    If blnMultiple = True Then
    strCriteria = strCriteria + " AND [STATE] like '" & txtState.Value & "*'"
    Else
    strCriteria = strCriteria + "[STATE] like'" & txtState.Value & "*'"
    blnMultiple = True
    End If
    End If
    End If
    If Not (IsNull(txtOwner.Value)) Then
    If blnMultiple = True Then
    strCriteria = strCriteria + " AND [OWNER_OPERATOR] like'" & txtOwner.Value & "*'"
    Else
    strCriteria = strCriteria + "[OWNWER_OPERATOR] like'" & txtOwner.Value & "*'"
    End If
    End If
    lstMember.RowSource = "SELECT DISTINCTROW [qryTelcos].[TELCO_HOTEL_ID],[qryTelcos].[OWNER_OPERATOR],[qryTelcos].[address_1], [qryTelcos].[CITY], [qryTelcos].[STATE], [qryTelcos].[country_name]FROM qryTelcos " & strCriteria & "Order by [qryTelcos].[address_1]"
    lstMember.Requery
    End Sub

    For all intents and purposes, it works. However, in my list box, I have it showing "Owner Operator", "Address", "City", "State", "Country Name".

    However, When I do my search, if the record does not have a State associated with it, (e.g. Paris, France), it will not show the record. What am I missing here? (Did I mention that my VBA skills are at a novice level, at best?)

    Thanks in advance for your assistance.

    TS

  5. #5
    Join Date
    Jan 2004
    Posts
    6
    Just want to mention a quick discovery I've made in dealing with this issue. I commented out all the If statements dealing with "State" and the filter works perfectly. Is there something going on in the syntax of that line of code that is keeping it from displaying records that do no have a state associated with it?

    If Not (IsNull(txtState.Value)) Then
    If blnMultiple = True Then
    strCriteria = strCriteria + " AND [STATE] like '" & txtState.Value & "*'"
    Else
    strCriteria = strCriteria + "[STATE] like'" & txtState.Value & "*'"
    blnMultiple = True
    End If
    End If

    Thanks again,

    TS

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the thread is long, it's dinner time and my wife is not a fan of me playing here! so to cut a long story short: odds-on you have a superfluous and problem.

    if the first test isnull() is true but the second is not, you end up with:
    WHERE and BlahBlah.

    the magic trick (i first saw it from Rudy) is to start off with:
    strWhere = "Where 1=1 "
    and in all subsequent tests:
    strWhere = strWhere & "and BlahBlah

    voila!

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2004
    Posts
    6
    Izy,

    Sorry to be a pain, and interupt your dinner, but your solution was not really clear. Like I said, my VBA skills are not really all that. Could you please clarify?

    Thanks,

    TS

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    post-prandial:

    the problem i was addressing (still not sure if it's your problem or your only problem).

    strWhere = "WHERE "
    if onething then strWhere = strWhere & "this = mythis "
    if twothing then strWhere = strWhere & "and that = mythat "
    if threething then strWhere = strWhere & "and other = myother "


    ONLY works if onething is True
    LATER EDIT: or all are False if you handle len(strWhere) < 6 EVEN LATER: <7 not six

    if onething is False, but twothing, threething are True, your strWhere reads:
    "WHERE and that = mythat and other = myother"
    ...which don't make sense!!!

    ...you have a lost "and"

    rewriting as: (thank God for copy/paste!)
    strWhere = "WHERE 1=1 "
    if onething then strWhere = strWhere & "and this = mythis "
    if twothing then strWhere = strWhere & "and that = mythat "
    if threething then strWhere = strWhere & "and other = myother "


    if onething is False, but twothing, threething are True, your strWhere reads:
    "WHERE 1=1 and that = mythat and other = myother"
    ..which is fine! works for any, all, none of the test conditions being True

    izy
    Last edited by izyrider; 02-18-05 at 14:19.
    currently using SS 2008R2

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by izyrider
    the magic trick (i first saw it from Rudy) is to start off with:
    strWhere = "Where 1=1 "
    izy
    nice one Izyrider / Rudy

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by healdem
    nice one Izyrider / Rudy
    ... And a nice one from Uncle Mike is : WHERE (1=0)

    Useful for adding a blank record in a recordset ...

    SELECT * FROM SomeTable WHERE (1=0);
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    May 2006
    Posts
    4
    turtlesplatter, you say you have a 'textbox based off a query'? what do you mean by based? how do you base it off a query?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •