Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: Query using wildcards?

    I'm pretty new to using queries. I'm currently trying to create a simple query for one table on one field. The field I want to query is a product number. I want to use a search that can pull the matches or close matches for the product number that is searched for. The product numbers have differing lengths, some with spaces, others with dashes or underscores and typically start and end with a letter.

    As an example, here are a few product numbers used in the DB:

    cml 10-160sgzi
    cml 16sg
    ml25sg
    ml2_8sg

    What wildcards would need to be used for the criteria? Would it need to be:
    "?*?" used with the like operator?

    Thanks in advance for any help.
    -Dman100-

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    cml 10-160sgzi
    cml 16sg
    ml25sg
    ml2_8sg

    Here is a couple to try in the query criteria row. Put Like in front of each of them. Actually if you don't Access will do it for you

    "cm*"

    That gets all the things that start with cm

    "*gzi"

    Everything that ends in gzi

    "*16*"

    Everything that has 16 someone in your entries

    *?

    Gets everything where there is an entry.

    "*m*a*"

    Gets everything with and m and an a but they don't have to be next to each other.

    "*_*"

    Gets anything with a _ somewhere in the middle.

    Best to play about with them yourself but there a few to play with.

    Mike

  3. #3
    Join Date
    Jan 2004
    Posts
    124
    Mike,
    Thanks for replying to my post.

    I'm completely new to writing queries, so I apologize for my lack of understanding about queries and how they are built. What I'm trying to do is allow someone to type in a search string in a text field on a web form and search the database field for a match or the closest match to the search string that was typed in.

    You mentioned that *? would retrieve everything where there is an entry.

    How can I modify that to search for the closest matches or an exact match if it is in the database?

    Am I explaining correctly?

    I appreciate your help. Thanks.
    -Dman100-

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I don't know anything about web forms but assuming a query can be fed criteria from one then I think probably c* or a* etc.

    The more letters before the asterisk the less records returned.

    For example M* gets Mike, Mark, motor, master etc. But Mi* gets Mike, Michelle and Mitch but not motor, Mark or master.

    But if the web form works like a form in Access then things will be different. A query can have its criteria established by reference back to entries ina form. But things like Mi* no longer work.

    What you probably need is a drop down combo box list. They are very simple to make on an Access for but have no idea of what happens with a web form. The drop down list gives two options

    1) You click the arrow and scroll through the listings and then click on the one you want and Access then takes you to the record in the form

    2) You can start typing and Access will bring to the top the first thing that approximately matchs. The underlying list that supports the combo will normally be in ascending order sort. So if you type Mi then if Mike was in the list it would come to the top.....unless Michelle was also in the list and then it would come to the top.

    Mike

Posting Permissions

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