Results 1 to 8 of 8
  1. #1
    manoman is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4

    Not Like is not working

    Hello,



    im building a query and I have two tables. I’m trying to use the data in my second table to eliminate the records I don’t want to see in the first table. I have added the corresponding field to the grid and put the following in as my criteria.

    Not Like “*” & [tblone]![fieldone] & “*”
    and it returns all the records duplicated many times.

    As a test I used:
    Like “*” & [tblone]![fieldone] & “*”
    and it returns only the results that I don’t want to see, which is what I’m trying to eliminate from the results.

    i tried joining the fields all three ways, fail.
    i tried both long and short text as field types, fail.
    i tried not using the second table and using, in the criteria field, Not Like “example01” And Not Like “example02”, etc, for each piece of criteria and the statement is too long and is truncated, fail.
    i also tried Not Like “*” & [tblone]![fieldone] & “*” Or Is Not Null, fail

    any help would be appreciated.
    manoman

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sounds like the unmatched query wizard could solve your problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    manoman is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4
    Hi pbaldy and thanks for the advice. I tried the unmatched wizard and it doesn’t like the long text and for some reason it doesn’t recognize the field in the second table.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You're going to have problems with long text fields, there is a very limited number of circumstances where you can actually display the contents and search the contents of them for relevant information. The only way you'll reliably get the information you want (as far as I know) from a long text field is if the string you're searching for is in the first 255 characters of the line. If not you're out of luck. You may be able to recursively search the value of the long text field with code, but I've never tried that approach but if you can that might provide the solution just by adding an indicator to the records matching your criteria.

  5. #5
    manoman is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4
    Hi rpeare and thanks for your reply. Based on your advice I’ll probably take a different approach and create a delete query to delete the records I don’t want and look at the results. I still find it frustrating that just by adding the Not to the beginning of the criteria doesn’t generate the expected result, even when I tested it and changed the field to short text. I figured I was using the wrong wild card for Not but I tried % and ? as well, same result.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you can I would post a copy of your database here minus any private information. Just something enough to reproduce the error. Someone might be able to give you a more satisfactory answer.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    In a Find Unmatched, the criteria would be IS NULL or NOT IS NULL depending on what you want to find, no wildcard.

    Maybe:

    SELECT * FROM table1 WHERE ID NOT IN (SELECT ID_FK FROM table2 WHERE some criteria here);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    manoman is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4
    Thanks again rpeare and thank you June7!

    this worked for me as long as I used the exact criteria.

    SELECT tblOne.fldOne
    FROM tblOne LEFT JOIN tblTwo ON tblOne.fldOne = tblTwo.fldTwo
    WHERE (((tblOne.fldOne) Not In ([tblTwo].[fldTwo])));


    Regards,

    manoman

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

Similar Threads

  1. Replies: 2
    Last Post: 05-14-2017, 10:07 AM
  2. Working query stops working after importing
    By Abacus1234 in forum Import/Export Data
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  3. Replies: 13
    Last Post: 01-22-2015, 05:27 PM
  4. Replies: 1
    Last Post: 12-27-2014, 12:38 PM
  5. Replies: 3
    Last Post: 01-29-2013, 04:34 AM

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
  •  
Other Forums: Microsoft Office Forums