Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: how to delete particular records?

    i have created this database in access. i want to delete all the records from a table in that database which DO NOT have a predefined value in some particular field.
    for example:-
    i use the query, DELETE FROM table_name WHERE {condition}
    and the condition would be that the value at a field is not equal to some value.
    say, for instance, that the table Table1 has a field, Name, which contains the values Steve, Nicole, Bruce, Carl, etc. what should i do in order to delete all the records which do not have the value in their Name field as Nicole?
    i've tried the query, DELETE FROM Table1 WHERE Name!='Nicole', but seems that it works only in oracle and not in microsoft access.

    any suggestions would be appreciated....

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: how to delete particular records?

    Originally posted by smartalec
    i have created this database in access. i want to delete all the records from a table in that database which DO NOT have a predefined value in some particular field.
    for example:-
    i use the query, DELETE FROM table_name WHERE {condition}
    and the condition would be that the value at a field is not equal to some value.
    say, for instance, that the table Table1 has a field, Name, which contains the values Steve, Nicole, Bruce, Carl, etc. what should i do in order to delete all the records which do not have the value in their Name field as Nicole?
    i've tried the query, DELETE FROM Table1 WHERE Name!='Nicole', but seems that it works only in oracle and not in microsoft access.

    any suggestions would be appreciated....
    This is an exerpt from a practice database that I use. This is run from a form module.

    CurrentDb.Execute "DELETE tblTemp.WONum FROM tblTemp " & _
    "WHERE tblTemp.WONum Not Like ""S00*"";"

    It works for me. Hope this helps.

    Gregg

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE Name <> 'Nicole'

    or

    WHERE NOT Name = 'Nicole'


    rudy

  4. #4
    Join Date
    Nov 2003
    Posts
    5

    thanks a million!!!

    basicmek and r937.
    i am very thankful to both of you!
    you guys rule!!
    now the code is working just fine. i'm so glad. had you guys not replied, i was considering creating a few more temporary tables in the database which would have been a hell lot of work.
    once again, thanks a lot!

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try also
    NOT IN("Nicole")

Posting Permissions

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