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

    Query use 'like ' syntax showing wrong results (2003)

    I am trying to do a query where words or sentences containing the word “COS” should be returned but for some odd reason the result in the attachment is being shown, which is incorrect. The correct result should be as follows:

    EVPN SVC - Telco Access: New Customer Request Pls upgrade the Accessline from 2 Mbit to 4 Mbit. COS 4 from 512 to 1024;1st Floor, LAN Room
    Customer Request Pls upgrade the Accessline from 2 Mbit to 4 Mbit. COS 4 from 512 to 1024;

    I am baffled as to why this is?
    Attached Images Attached Images

  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: Query use 'like ' syntax showing wrong results

    You will need to show us the query you are using. Either a screen capture of the query grid or the sql.
    Regards
    John



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

    Re: Query use 'like ' syntax showing wrong results

    as requested
    Attached Images Attached Images

  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: Query use 'like ' syntax showing wrong results

    I can't see anything wrong.

    <UL><LI>Is SWNotes the right field?
    <LI>You don't have any other criteria on other fields we can't see?
    <LI>Is the is Null criteria for DEC_Note removing records you want?[/list]Are you saying that the query is returning records that don't contain cos, and not returning some records that do?

    Is Customer Open a table or a query?
    Regards
    John



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

    Re: Query use 'like ' syntax showing wrong results

    In response to your questions:

    Are you saying that the query is returning records that don't contain cos, and not returning some records that do?
    It is returning only one record with COS, the others don't contain COS see 1st 3 records in screenshot. The last records is valid the rest are not.


    Is Customer Open a table or a query? Customer Open is a table

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query use 'like ' syntax showing wrong results

    I have a feeling it is because you are searching for COS, which is a trigometric function in Access and it is getting confused. It is a theory
    Jerry

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

    Re: Query use 'like ' syntax showing wrong results

    Is there a workaround to this problem?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query use 'like ' syntax showing wrong results

    Not really as far as I know as it is a reserved word, there is for fields but not for data. I have tried Like "*" & Chr(67) & "OS*" but again this fails. I can only assume that COS is a business requirement otherwise you could change it to XCOS or something to assist the criteria
    Jerry

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query use 'like ' syntax showing wrong results

    Right, a little more digging around

    COS returns NULL if passed a NULL value

    As we are send the perceived vale COS a NULL value, therefore the value of the Criteria of the string is NULL therefore it returns everything....I find it fascinating.

    Work around create new criteria for the filter.
    Jerry

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

    Re: Query use 'like ' syntax showing wrong results

    I am puzzled by this explnation.

    I just created a Like "*COS*" query in one of my dbs and it returned the records expected.
    Regards
    John



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

    Re: Query use 'like ' syntax showing wrong results

    Are you sure that there is not a "COS" somewhere out of sight? What happens if you increase the row height to allow you to see more text? or put the cursor in the field and press the down arrow.

    My understanding is that this is a memo field containing perhaps multiple sentences. All you see initially is the first line.
    Regards
    John



  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query use 'like ' syntax showing wrong results

    So am I John, so am I <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    I was at the College tonight and I sat in with the other lecturers and we played around with it. JJ1972 has these weird and wonderful data extracts where they are imported in from Excel a lot of the time in my experience. I have copied and pasted the string they have provided us in the upper part of this thread

    If I use the following on jj1972's text string:

    Like "COS*" works if starts with COS
    Like "*COS*" doesn't work

    If I do the same as you and use my own data, it works in both cases......As we are not presented with any data as usual and just screen dumps, I cannot suggest anything else
    Jerry

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

    Re: Query use 'like ' syntax showing wrong results

    I think l will have to leave as it and use excel it appears what l am trying to do is not possible.

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query use 'like ' syntax showing wrong results

    Are there any special characters in these strings?

    Any chance of a database with this in it?

  15. #15
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query use 'like ' syntax showing wrong results

    Hi jj1972

    At last I have now worked out why this is happening and it is what Patt, John have alluded to. The reason I have worked this out is because I have looked at you excel workbook in <post#=683,845>post 683,845</post#> and all of you text codes have carriage returns in the Char(10) to be exact. You cannot import data from an excel workbook with multiple lines of text wrapped in a cell.
    Jerry

Posting Permissions

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