Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Missing string query (2000)

    How do you set up a select query that omits records with a field that contains a certain string anywhere in the field?
    I have a field that is a string that has a certain expression somwhere in the string (not always in the same place). I discovered that I can use the Like function to select records that have the desired sub-string, but I need to select records that do NOT have the sub-string. I tried Not Like ...., but that doesn't work.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Missing string query (2000)

    Not Like should work. It does for me.

    When you say ti does not work, what happens? do you get an error. do you get too many records or not enough.

    SELECT Table1.field1
    FROM Table1
    WHERE (((Table1.field1) Not Like "*pink*"));


    Here is some Sql that gave the right results for me
    Regards
    John



  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Missing string query (2000)

    When I try Not Like "*string*" it returns no records. However, there are records that do not have "string" somewhere in that field. So I don't understand why it isn't working.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Missing string query (2000)

    I can't think of an explanation. Can you post the db, or a cut down version?
    Regards
    John



  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Missing string query (2000)

    Thanks, but right now I don't have the time to post a sample db, and I'm working around the problem. It's just a one shot deal, so I can handle it manually.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Missing string query (2000)

    Try this instead:

    SELECT Table1.field1
    FROM Table1
    WHERE ((Instr(Table1.field1, "pink") = 0);
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Missing string query (2000)

    Thanks Charlotte, that works (except it needed quote marks around the 0, i.e. = "0")

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Missing string query (2000)

    Um... that doesn't make sense. The value returned by Instr is an integer, so it would never equal "0". <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  9. #9
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Missing string query (2000)

    Hi Charlotte,
    That was also my first thought! But it works! Even in the query's design view it shows a new "field" with the InStr function and down in the criteria area it simply has "0". My help file states that the InStr function returns a Variant, but can a variant be a string value? It is certainly a mystery.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Missing string query (2000)

    Variants can be anything, so it is quite possible it returns a text zero rather than in integer value.
    Wendell

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

    Re: Missing string query (2000)

    The Help file states that InStr results in a Variant (Long). If you use VarType to determine the data type of the return value of InStr, you get 3 = vbLong. But VBA is not strictly typed, so both

    WHERE InStr(...) = 0

    and

    WHERE InStr(...) = "0"

    are accepted as valid (I assume that the Access dialect of SQL passes InStr to the VBA engine). I can't believe that SQL/VBA would NOT accept the condition with = 0.

Posting Permissions

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