Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    127

    Unanswered: Not getting the result i require! please help

    I have a table in MS Access which contains a field with the following data

    REF_AGENT
    G9709755
    G9710179
    G9710612
    G9710818
    G9800560
    G9800890
    G9808386
    G9904244
    G9999998

    I would like to exclude every row apart from "G9999998"

    I am using the following criteria to pull the data.

    Not Like "G*" And Like "G9999998"

    but it is not returning anything... what am i doing wrong?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    im not surprised you re getting no rows returned using Not Like "G*" And Like "G9999998"

    so you want rows not like "G*"
    and
    rows like "G9999998"

    so as both terms are mututally exclusive, you are getting no rows returned.

    changing to
    where mycolumn like "G*" AND mycolumn ="G9999998"
    but that doesn't make logical sense
    either
    where mycolumn like "G*"

    or

    where mycolumn ="G9999998"
    would make sense
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try it with OR

    PHP Code:
    SELECT Tabelle1.Ref_Agent
    FROM Tabelle1
    WHERE Tabelle1
    .Ref_Agent Not Like "G*" Or 
        
    Tabelle1.Ref_Agent Like "G9999998*"
    This would select only G9999998

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by skhlaq768
    I would like to exclude every row apart from "G9999998"
    Code:
    WHERE  my_field = 'G9999998'
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2007
    Posts
    127
    thanks for the quick and thorough response, it makes sense....

    but i forgot to mention that i have records like.

    "H9999998", "H2184998"... and so on...

    so this will not work

    "Just enter "G9999998" in your criteria."

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why will it not work
    is there soemthing else you are doing that you forgot to mention

    the records starting with H are irrelevant to what you first posted


    Code:
    SELECT Tabelle1.Ref_Agent
    FROM Tabelle1
    WHERE Tabelle1.Ref_Agent Tabelle1.Ref_Agent Like "G9999998*";
    that provides what you originally said you wanted, unless what you meant to say is you want all records except those starting with "G", UNLESS those starting with a G are G9999998 in which case HAMBAKKA's suggestion should do the trick
    Code:
    SELECT Tabelle1.Ref_Agent
    FROM Tabelle1
    WHERE Tabelle1.Ref_Agent Not Like "G*" Or 
        Tabelle1.Ref_Agent Like "G9999998*";
    the where clause is resolved as a yes/no. if the row meets the criteria its included in the recordset, if it doens't it isn't

    so you need to master the basics of boolean logic for each where clause
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I agree with post 4 tbh.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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