Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    petes1505 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    34

    Ignore Specific String of Text

    Hello all,

    I’m hoping to get some help with a query. The query is pulling data from a payables table and I am trying to add criteria that will ignore a specific text string in the Description field of the source table.

    Here is an example of what I want it to ignore:
    ABC*Open Item Inc~

    I tried using Not Like “ABC*Open Item Inc~“, but the query not only ignores the string shown above but it also ignores anything with ABC or Open or Item….etc. I cannot seem to come up with the criteria that will allow the query to only ignore anything with the entire string and not just any part of the string.

    Additional note: I know the wildcard character is present in the text string, and there is nothing I can do about that as the source data is coming from an outside party.

    Any ideas would be appreciated.



    Thanks,
    Pete

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Try putting the * in square brackets to escape it

    NOT LIKE "ABC[*]OPEN ITEM Inc~"
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    petes1505 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    34
    Hi Minty,

    I just tried it but it did not change the results. The results are actually identical to when the brackets are not used.

    Thanks,
    Pete

  4. #4
    petes1505 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    34
    Here is the funny thing.....if I reverse things and use Like "ABC*Open Item Inc~" the query pulls up only lines with that exact string. As soon as I change it to Not Like, it pulls up other data.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Look closely at your data as there might be something about it or in it that you're not seeing.
    - is this an rtf formatted memo field?
    - expand the row height to look for line wraps within a field record

    Making those points because what Minty posted worked for me.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    petes1505 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    34
    It is not working for me. To test it I changed the criteria from Not Like to Like and used the brackets, and the query does not pull up any data. When I removed the brackets and run the same query (using LIKE), it pulls up the correct lines with the exact string.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can you post a zipped copy of the db? You could remove everything from the copy that is not needed to replicate the issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    petes1505 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    34
    I wish I could, but no. It is company information and I cannot share with an external party.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    would also help if you can provide the following example in your db

    what you want to ignore e.g. ABC*Open Item Inc~
    an example of what is returned that you don't want
    an example of what is not returned that you do want

    OK so you can't send the db but still provide some examples (made up if necessary but needs to be sufficient to replicate the issue) plus the full sql you are using in your query

  10. #10
    petes1505 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    34
    If it helps, the query is a simple select query. The table I am pulling data from is nothing more than a list of payments with multiple columns (text), and one of the fields is a description fields that include standard text. The example I provided is exactly what you would see for the specific records I'm trying to have the query ignore. There really isn't anything complex......just a very basic query.

  11. #11
    petes1505 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    34
    an example of what is returned that you don't want
    an example of what is not returned that you do want
    The results are totals, not line-by-line detail. For example, say the total spend is $1,000 and the lines I am trying to ignore total $200. When adding the criteria I'm expecting the query to return $800. It's not....it is returning something less than $800.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, if you can't provide anything at all that would allow examination of just a few records in one or two fields and allow us to replicate the issue, I don't see how I can help beyond the suggestions I made in post 5 that were not commented on. Wish you luck.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Shouldn't that be Not Like "ABC*Open Item Inc~" & "*" (or Not Like "*" & "ABC*Open Item Inc~" & "*" if the "ABC.." is not at the beginning of the field).
    Here is a sample db.
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    petes1505 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    34
    Quote Originally Posted by Gicu View Post
    Shouldn't that be Not Like "ABC*Open Item Inc~" & "*" (or Not Like "*" & "ABC*Open Item Inc~" & "*" if the "ABC.." is not at the beginning of the field).
    Here is a sample db.
    Cheers,
    Yes, it is. From my original post:

    ABC*Open Item Inc~

    Here is an example of what I want it to ignore:
    ABC*Open Item Inc~

    I tried using Not Like “ABC*Open Item Inc~“, but the query not only ignores the string shown above but it also ignores anything with ABC or Open or Item….etc. I cannot seem to come up with the criteria that will allow the query to only ignore anything with the entire string and not just any part of the string.

    Additional note: I know the wildcard character is present in the text string, and there is nothing I can do about that as the source data is coming from an outside party.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I know the wildcard character is present in the text string, and there is nothing I can do about that as the source data is coming from an outside party.
    Must it remain or can you run an update query to replace it with a space? What happens if you remove it from a couple of records and re-test?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 06-12-2020, 08:31 AM
  2. Extract specific text from very long string
    By csmith in forum Queries
    Replies: 24
    Last Post: 03-03-2017, 09:25 AM
  3. Replies: 6
    Last Post: 05-29-2015, 10:21 AM
  4. Trying to find a value right of specific string with in text
    By weilerda in forum Import/Export Data
    Replies: 2
    Last Post: 10-18-2012, 12:58 PM
  5. Replies: 13
    Last Post: 07-05-2012, 10:33 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