Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Like clause in query not working (2003)

    I am trying to do a query where l need to meet two conditions of a 'like' clause and it is not working. I would like to remove any occurances of the word 'EVPN' as shown in the screenshot and keep any occurance of the word 'Rob' in the SWNOTE field.

    See attachments for details, any help would be appreciated. I have tried but the reault l get as that either of the condition matches and not both which is what l require.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like clause in query not working (2003)

    Hi
    I think the problem is that you have the two criteria on different lines; they should be joined with and AND on the same line.
    I have attached a DB which contains a noddy table and query which seems to do what you want.
    Hope so
    Silverback
    Attached Files Attached Files
    Silverback

  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like clause in query not working (2003)

    Hello,

    Thanks for that, this still does work as required as l require those rows in the Note field which contain the both the word 'Rob' and ATTADMIN in the ID field. Also l need to pick up rows which have the following in the ID table, also these might or might not contain Rob, if they do or not they still need to be retrieved.

    "NLIBP9AM5" Or "NLIBP9AM4" Or "NLIBP9AM1" Or "NLIBP9AE1" Or "DKIBP9LY1" Or "DKIBP9AD1" Or "DKIBP9AG1" Or "DKIBP9AS1" Or "NOIBP9TM1"

    See attachment for revised database.
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Like clause in query not working (2003)

    I'm not sure what you intent was, but the way you've designed the query essentially has 2 conditions that will allow a record to be shown.

    Condition #1:
    Where SWNOTE LIKE "*Rob*" (that is, if it meets this condition it ignores Condition #2)

    or

    Condition #2:
    Where SWNOTE Not Like "*EVPN* and Site_ID is one of the choices you listed.


    Is this what you intended?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like clause in query not working (2003)

    Hello,

    It needs to meet both these conditions i.e.

    Condition #1:
    Where SWNOTE LIKE "*Rob*" and ID like "ADMIN"

    AND

    Condition #2
    Where SWNOTE Not Like "*EVPN* and Site_ID is one of the choices you listed.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Like clause in query not working (2003)

    >>It needs to meet both these conditions <<

    That's why I asked. You can't conveniently use the query grid to accomplish this. Go to the SQL view and modifiy the the WHERE clause to be like this:

    WHERE (SWNOTE Like "*Rob*" AND ID = "Admin") AND (... your condition #2...)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like clause in query not working (2003)

    I tried this and got error, see attached.
    Attached Files Attached Files

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Like clause in query not working (2003)

    I can't debug your statement for you. You probably have a problem with unbalance parentheses. There are simpler ways to do this other than that horrendous "OR" statement. You could use an IN statement, or better yet, create another table with 1 record for each of those ID #'s in the OR clause, then JOIN to this table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Like clause in query not working (2003)

    I looked at this again, and even if you get the syntax error fixed, your query will never return anything!

    The Condition #1 has in it Site_ID = "Admin"

    Condition #2 has a long list of Site_ID to consider, but NONE of them include "Admin"!

    So a record with a SWNOTE that happens to be exactly "Rob" will pass condition #1, but will fail Condition #2!

    I have no idea what you are trying to do, but this query won't do it. Maybe you need to try to just explain it in words first.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like clause in query not working (2003)

    It was meant to be ATTADMIN in the query and not ADMIN, my mistake!

  11. #11
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like clause in query not working (2003)

    Hello,

    The correct term should be ATTADMIN and not ADMIN, see screenshot for error l am getting .

    The criteria l require is this:

    Where SWNOTE LIKE "*Rob*" and IS like "ATTADMIN"

    And where SWNOTE Not Like "*EVPN* and Site_ID is either "AA" OR "BB" OR "CC"
    Attached Files Attached Files

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like clause in query not working (2003)

    The error is caused by Access prompting you for a parameter becasue you have mistyped the first element of the WHERE clause to be WHERE [tbl COS report]. SITE_ID. SWNOTE.
    I think it should be just WHERE [tbl COS report].SWNOTE
    Thinking about the criteria!
    Silverback
    Silverback

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like clause in query not working (2003)

    Hi
    Does this do what you want?
    Silverback
    Attached Files Attached Files
    Silverback

  14. #14
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Like clause in query not working (2003)

    I don't care if it is ATTADMIN or just ADMIN. From the way you've established your criteria, I doubt you will find any record that meets both condition #1 AND condition #2!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  15. #15
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Like clause in query not working (2003)

    Thatís great, l was nearly there but not quite!

    I have a small problem which l need a bit of assistance on now, that is l need to remove duplicate rows of data, see attatched for screenshot for what l mean.
    Attached Images Attached Images

Page 1 of 2 12 LastLast

Posting Permissions

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