Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Expression Builder (Access 2000)

    I am trying to allow a search where a user enters an item number and it pulls up all the records that have that item number in its field. Right now the formula in the expression builder is:
    Like "*" & [Forms]![Master_Table_Builder]![Item_Number] & "*"
    For each record, there are multiple item numbers saved in a field called "Table_Item_Number". Multiple records are stored in the format:
    1-2,2-2,11-1
    where each item number is separated from the next item number with a comma. There can be one to many item numbers per record. My current expression, not surprisingly, picks up to many records. For example, searching for: 1-2 will correctly show the record with
    1-2,2-2,11-1
    but it will also show an addition record with
    1-21,1-3
    or
    11-2,11-3,11-4,11-8
    In the example 1-2 is like 1-21 and 11-2. Does anyone have an expression that will work for this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Expression Builder (Access 2000)

    You could use

    Like [Forms]![Master_Table_Builder]![Item_Number] & ",*" Or Like "*," & [Forms]![Master_Table_Builder]![Item_Number] & ",*" Or Like "*," & [Forms]![Master_Table_Builder]![Item_Number]

    Problems like this are precisely why it's not a good idea to store multiple values in a single field. Better to have a subtable with a separate record for each item number.

  3. #3
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Expression Builder (Access 2000)

    I created a sub table, which appears to work, but I have run into a new problem. I have created a form that searches 9 fields. 3 of the fields are the table Item Number fields. I want the form to be able to perform a multisearch on a combination of one to all of the fields. I am having two problems right now. First, when I search using the Table Item Fields, records that have a null value for the other search fields do not show up. Second, I do not know how to allow a multisearch if you don't want to search using the table item fields. I have created three seperate fields that are called Item_1, Item_2, and Item_3. In the query I have the table and subtable linked. For the Table Item searches, my expression is:
    [Forms]![Defect_Table_Builder]![Item_1]
    [Forms]![Defect_Table_Builder]![Item_2]
    [Forms]![Defect_Table_Builder]![Item_3]
    The rest of my expressions for the rest of the fields look like:
    Like "*" & [Forms]![Defect_Table_Builder]![Tested] & "*"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Expression Builder (Access 2000)

    I'm confused by your description. Are Item_1 etc. fields in a table or text boxes on a form, or both?

    You may be able to use criteria such as

    [Forms]![Defect_Table_Builder]![Item_1] Or [Forms]![Defect_Table_Builder]![Item_1] Is Null

    what exactly do you mean by "to allow a multisearch if you don't want to search using the table item fields"?

  5. #5
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Expression Builder (Access 2000)

    Item_1 etc are both fields in a table and text boxes in a form. By multisearch I mean giving the ability to the user to search more than one field in a table to narrow down search results. However, most of the database users are only going to need to search two or three of the fields at once and not all nine of them. Your expression does solve one of the problems. I think I am going to solve the other problem with null fields in records by not allowing null fields. Thanks for your 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
  •