Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2014
    Posts
    9

    Unanswered: Wildcard in Query Criteria

    I have a text field "DESCRIPTION" where the user inputs a value on a form to search for a part number within that field. Example. 4060
    Note: Part numbers can also be Alphanumeric i.e 406ab4

    My criteria on the query is:
    ALike "%" & [Forms]![frm_SrchDescForPart]![PartNumber] & "%"

    This is not working correctly because in the above example I get results where "4060" may be part of a longer number - 050324060

    There will also be cases where the Part Number does not have a space in front of it but a character. ie.. SD-4060, this is fine as it is the number that was being searched.

    I want a criteria that will return all records that have the form input data anywhere within the text field except if there are other numbers before or after it.

    Can this be done with a qry criteria?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by MsRedbutter View Post
    My criteria on the query is:
    ALike "%" & [Forms]![frm_SrchDescForPart]![PartNumber] & "%"

    This is not working correctly because in the above example I get results where "4060" may be part of a longer number - 050324060
    If you criteria is
    Code:
    A Like '%4060%'
    050324060 is a valid answer.

    If you need to specify more complex search patterns, you can use regular expressions, see: Like Operator - Access
    Have a nice day!

  3. #3
    Join Date
    Aug 2014
    Posts
    9
    I'm sorry. Maybe I'm not being clear. I don't want to find the number within another number. I want to find it exactly as it's written.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Don't think access supports regular expression matching..... Offhand I can't think of an 'elegant' way of doing it. There's possibly a brute force approach using 10 not like clauses...
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by MsRedbutter View Post
    I'm sorry. Maybe I'm not being clear. I don't want to find the number within another number. I want to find it exactly as it's written.
    Then remove the wildcards in the criteria.
    Have a nice day!

  6. #6
    Join Date
    Aug 2014
    Posts
    9
    If you remove the wildcards from the criteria it will look for the search filed to be exactly like what's on the form.

    Again I want to be able to find any sequence of alpha and/or numeric combinations within a text field.

    If the user inputs 456 and the search field contains the words: Part no is 456 or if it says Part No:456 or if it says Hot apply pie is $4.56 it should return in my search.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by MsRedbutter View Post
    If the user inputs 456 and the search field contains the words: Part no is 456 or if it says Part No:456 or if it says Hot apply pie is $4.56 it should return in my search.
    This is not precisely the same as:
    Quote Originally Posted by MsRedbutter View Post
    I want to find it exactly as it's written.
    You may try using regular expressions, although Access does not support the whole set of them. If it does not work, you'll have to write your own string parser.
    Have a nice day!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If the user inputs 456 and the search field contains the words: Part no is 456 or if it says Part No:456 or if it says Hot apply pie is $4.56 it should return in my search.
    the to my mind it suggests there may be a design problem

    returning anythign that includes 456 as 3 contiguous symbols easy enough
    Code:
    where mycolumn like '%456%'
    returning anything that is 'sort of' like 456 eg 4.56, 45.6 (0.456 and 456.0) woud be found by the first example

    I'm not certain you can do this using regular expressions
    you may well have to decompose the requirement if numeric to be
    Code:
    where
      mycolumn like '%456%'
      or mycolumn like '%4.56%'
      or mycolumn like '%45.6%'
    Last edited by healdem; 09-22-14 at 15:52.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2014
    Posts
    9
    I figured out what I needed.

    This works as a criteria in my query!

    ALike "%" & "[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]" & "%"

Tags for this Thread

Posting Permissions

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