dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] how to make the search as case sensative

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    10

    Resolved [RESOLVED] how to make the search as case sensative

    HI
    I have an access database connected to vbform. In txtsearch box i fill the following code :


    msql = "select * from dictionarylibrary where sword like '%%%" & txtSearch.Text & "%%%'"

    it works this manner Suppose i enter word "Pick" in search box, then the result box will also show words handpick, pick, pickle, toothpick etc. means it take the matching words from the list though it may be in start or rear or middle.

    The following code give result that it show the words start from pick and Pick
    msql = "Select sword from dictionarylibrary where sword like '" & txtSearch.Text & "%'"

    I want that when I enter the word Pick then list should show words only start from Pick not from pick, pIcK, PiCK etc means the result box should show the exact match with the text box, we can call it case sensitive search.

    please help me. Thanks.

  2. #2
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: how to make the search as case sensative

    Rajat

    One brute force approach would be to use your red SQL statement and
    dump the results into an array. Then, loop thru the array, and allow only
    those that match, using something like

    Code:
    kword = txtSearch.Text
    klen = Len(kword)
    If Left(aaRawList(ii), klen) = kword Then
        ,, add it to your "real" list
    End If
    Spoo

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    10

    Re: how to make the search as case sensative

    sorry, i could not get properly.....where to add this code ???

  4. #4
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: how to make the search as case sensative

    Rajat

    There are many ways you could do it, so I didn't specify.
    Basic steps ...

    1. create 2 arrays .. one for RawList, one for GoodList .. oversize them, say 100 elements each
    2. issue your SQL statement
    3. loop thru the the returned SQL recordset and put each record into the RawList array
    4. loop thru the RawList array and apply the test I suggested in post #2, putting those that "pass" into the GoodList

    Can you take it from here?

    Spoo

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,857

    Re: how to make the search as case sensative

    I don't know of any way to make Jet SQL do a case-sensitive Like outside of Access itself where you can call a VBA function inline within the SQL. So as far as I know you have to get clever.

    In your case the pattern is a simple one, so this might work for you:

    Code:
    Private Sub QueryAll()
        Query "SELECT * FROM [Items]"
    End Sub
    
    Private Sub QueryInsensitive()
        Query "SELECT * FROM [Items] WHERE [FirstName] LIKE '" & txtPrefix.Text & "%'"
    End Sub
    
    Private Sub QuerySensitive()
        Query "SELECT * FROM [Items] WHERE " _
            & "StrComp(Left$([FirstName], " & CStr(Len(txtPrefix.Text)) & "), '" _
            & txtPrefix.Text & "', 0) = 0"
    End Sub
    Name:  sshot1.png
Views: 37
Size:  5.4 KB

    Name:  sshot2.png
Views: 39
Size:  5.0 KB

    Name:  sshot3.png
Views: 39
Size:  4.8 KB
    Attached Files Attached Files

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    10

    Re: how to make the search as case sensative

    "SELECT * FROM [Items] WHERE " & "StrComp(Left$([FirstName], " & CStr(Len(txtPrefix.Text)) & "), '" & txtPrefix.Text & "', 0) = 0"
    This code works exactly i wanted....... thanks Dilettante .... Thanks to Spoo also for his replies my query is resolved....... ....long live vbforums

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width