Results 1 to 4 of 4
  1. #1
    J Bhujanga is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2016
    Location
    Colorado
    Posts
    27

    Testing for Multiple Strings


    I have some cases where I want exclude or include data values that contain any one of a number of values. For example, I might want look for text fields where they contain 10, 12, 17, 21, 27 or 36.
    I can say "iif(SeachString not like "*10*" and SearchString Not Like "12*" and....
    This can be very cumbersome for a lot of values.
    Alternately I can do something like Iif(Instr(1,"10_12_17_21_27_36",SearchString)=0...
    This is good but can be messier if the strings I'm trying to dis-include are actual phrases.
    Is there a more straightforward way to approach this? Like a method that let's you list values that are to be tested for?

    For when I'm looking for phrases that must include any one of a bunch of values it would be like above except "like" instead of "not like" and "Or" instead of "And".

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    put your values in a table and have a Cartesian query (no joins)

    tblA
    fld1
    12345
    34567
    45643554
    76455344

    tblLike
    fld1
    10
    12
    17

    SELECT tblA.*
    FROM tblA, tblLike
    WHERE tblA.fld1 like "*" & tblLike.fld1 & "*"

    or if you want a join (but this will not be viewable in the query grid)

    SELECT tblA.*
    FROM tblA INNER JOIN tblLike ON tblA.fld1 like "*" & tblLike.fld1 & "*"

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I might want look for text fields where they contain 10, 12, 17, 21, 27 or 36.
    I take it this means the characters are part of the field value, not the entire value. In that case, there are likely a few ways to skin this cat. One would be to assign the values to an array and loop through the array. What you'd do with this approach depends on the desired outcome and the strings you're working with. Build a recordset and update it? Run sql to append or update?

    Or maybe you just use LIKE with wild cards to find text phrases within the field contents?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    J,

    I'm not quite sure what your objective is, but if you're only dealing with 1 or 2 tables, you might check the modified Universal search to see if it might be useful. It looks for strings/fragments in all fields all tables, so may not e appropriate.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Testing for multiple conditions help
    By JHarmon in forum Programming
    Replies: 3
    Last Post: 02-22-2016, 04:16 PM
  2. Replies: 3
    Last Post: 01-19-2016, 07:39 PM
  3. Replies: 1
    Last Post: 08-13-2015, 08:02 AM
  4. Replies: 2
    Last Post: 01-22-2014, 09:38 AM
  5. Replies: 1
    Last Post: 09-16-2013, 04:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums