Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: Few Problems : Search Button Form

    Hi! Actually I had a code that the use is, I will search for a multiple data that can be separated by comma.

    For Example : I put [apple, banana] on my textbox(exact as what you see on the []) and I have a record on my database named :
    ♦apple1
    ♦apple2
    ♦apple3
    ♦banana2
    ♦banana1
    ♦carrot1
    ♦carrot2

    and when I press the Search Button it will show me all records that has APPLE and BANANA in the table.

    but the problem of my code is when I put a value on the textbox like BANANA or whatever word it is. It will just filter my 50k+ record into 300records which is surely wrong, because it must show me the record that has BANANA word in the table.

    Here's the code :

    LEGEND :
    Command26 - Button that I consider as a Search/Filter Button
    Text24 - Textbox where will I put the value that I'm looking
    Card_Number - Name of the column where what I' am searching is Located.

    Code:
    Private Sub Command26_Click()
    		Dim strFilter As String, strFilters() As String
    		Dim intX As Integer
    	 
    		With Me
    			strFilters = Split(Nz(.Text24, ""), ",")
    			For intX = 0 To UBound(strFilters)
    				strFilter = strFilter & _
    							Replace(" OR ([Card_Number] Like '%F*')", _
    									"%F", strFilters(intX))
    			Next intX
    			.Filter = Mid(strFilter, 5)
    			.FilterOn = (.Filter > "")
    		End With
    End Sub
    I'm still playing on the code, but still I need someones help. Thank you in advanced.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I would not rely on users entering the correct list spacing, and I am not sure why you have used a Replace() function.
    The following assumed users have used a comma correctly and then I have used the Trim() function to remove unspecified spaces between elements (if any!). It then check that there is somthing left to filter on ie. ignores just space before, between and after the commas!
    I think this is basically what you had, but is a little simpler and a little more user sympathetic!
    Code:
    Private Sub Command26_Click()
       Dim strFilter As String, strFilters() As String
       Dim intX As Integer
    
       With Me
           strFilters = Split(Nz(.Text24, ""), ",")
           For intX = 0 To UBound(strFilters)
                strFilters(intX) = Trim(strFilters(intX))
                If strFilters(intX) > "" Then
                    strFilter = strFilter & " OR [Card_Number] Like '" & strFilters(intX) & "*'"
                End If
           Next intX
           .Filter = Mid(strFilter, 4)
           .FilterOn = (.Filter > "")
       End With
    Not sure if that is of any help as you have not asked a specific question.

    MTB

  3. #3
    Join Date
    Feb 2012
    Posts
    14
    You might be able to simplify it very slightly by making an IN() query rather than chaining ORs together.

  4. #4
    Join Date
    Mar 2012
    Posts
    4
    Hi! still my problem is whenever I put a value on the textbox. It still filters to 300 records and not look for the value that I putted?

  5. #5
    Join Date
    Mar 2012
    Posts
    4

    Thumbs up

    Hi there! actually I finished this project yesterday and I forgot to reply here. BTW the problem is on me and not in the code, because I'm putting the code on a search box on my form and not a search box in the subform, so when I try to put the code on the textbox on the subform, It works like a boss!

    BTW here's the database. (Actually sample database for those new to access like me)
    Attached Files Attached Files

Posting Permissions

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