Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete question (Access 2000)

    I need help with the delete commands in sql.Out of two tables i want to delete those orders that have an order date less than 01.01.2006 and that correspond only to customer number 118,119 and 120.
    The query built by me somwhow does not work:
    Dim SQL As String
    SQL = Delete orders.orderid, orders.orderdate, orders.customerid, customers.Customerid FROM orders INNER JOIN customers ON orders.customerid = customers.Customerid WHERE (((orders.orderdate)<#1/1/2006#) AND ((customers.Customerid) In (118,119,120)))
    CurrentDb.Execute SQL

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete question (Access 2000)

    You shouldn't have the Customers table in this query. You cannot delete records from two tables at the same time, and you don't need it since you can put the criteria on the CustomerID field from the Orders table. The best way to develop code like this is to create the query in design view first, and test it until it works correctly. Then switch to SQL view to see what the SQL string is.

    SQL = "DELETE OrderDate, CustomerID FROM Orders WHERE OrderDate < #01/01/2006# AND CustomerID In (118,119,120)"

  3. #3
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete question (Access 2000)

    Thanks.Much appreciated.You also gave me insight into the problem.I think this is a unique site.Both comptetent and quick.

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

    Re: delete question (Access 2000)

    Could you also use :
    DELETE * FROM ...........

    What is the difference between the * and defining variables?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete question (Access 2000)

    DELETE Field1, Field2 FROM ... WHERE ...
    and
    DELETE * FROM ... WHERE ...
    and
    DELETE FROM ... WHERE ...
    are all equivalent, and all are valid SQL. But the query designer in Access will always list the fields you impose conditions upon. I conformed to this, not to confuse the original poster further.

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

    Re: delete question (Access 2000)

    Thanks

Posting Permissions

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