Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2012
    Posts
    16

    Unanswered: Like Operator to search middle of string

    Hi,

    I am trying to use the like operator in a query to search for only the middle characters in a string where the input comes from a text box.

    Any help would be greatly appreciated!

    Thanks in advance!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    "<ColumnName> Like '*" & <SearchString> & "*'"
    Ex (matches "Stevenson", "Stevens", "Braivenberg", ...):
    Code:
    "LastName Like '*ven*'"
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Posts
    16
    I could not get that method working for my scenario.

    I have tried placing in the criteria section of the query: Like "*" & [forms]![form1][test] & "*" And Not Like "*" & [forms]![form1]![test] And Not Like [forms]![form1]![test] & "*"

    This has a flaw, where it will rule out the name "Maria" if I input an "a"into the text box since Maria ends with an "a"

    Can the Mid function work with the like operator?

    Thanks for your time!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Mid cannot be used as a comparison operator. You can extract a substring from a string and compare that substring to whatever pattern you want, but you need to supply the first and optionally the last character positions of the substring into the string. See: Mid Function (Visual Basic)
    Have a nice day!

  5. #5
    Join Date
    Apr 2012
    Posts
    16
    Do you know of any other way to do this without extracting characters?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try using regular expressions with the Like operator, although they are rather limited in Access. What exactly do you try to search for and what do you want to exclude from the criteria?
    Have a nice day!

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Try only doing the like after first 3 characters have been type in.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Apr 2012
    Posts
    16
    I am looking for all results in a field that match the middle characters. For example if I typed in the text box "a", I would want results like Mary, Maria, Mark, Aaron but not Ava, Mona, Angie. Is that possible?

    Also, as a side note: is it possible to like search that is case sensitive?

    Thanks again!
    Last edited by WildBB; 04-20-12 at 17:00.

  9. #9
    Join Date
    Oct 2009
    Posts
    204
    use
    =Left(Right([text0],Len([text0])-1),Len(Right([text0],Len([text0])-1))-1)

    instead of
    like "*" & [text0] & "*"
    or
    like "*a*"

  10. #10
    Join Date
    Apr 2012
    Posts
    16
    Was that supposed to go in the query criteria field? It is giving me an error: the expression is typed incorrectly, or is too complex to be evaluated. Thanks for your responses!

  11. #11
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    What about using the ? Placeholder

    Like "?a*"
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  12. #12
    Join Date
    Apr 2012
    Posts
    16

    Solved!

    Thanks myle!

    Using placeholders (?) in the front and back worked.

  13. #13
    Join Date
    Apr 2012
    Posts
    16

    Case specific?

    Is it possible to use the like function with asc( ) to create a "case specific" query search with input from a form text box?

    Thanks for the help!

Posting Permissions

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