Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete query (Access 2000)

    In my delete query i get the error " specify the table containing the records you want to delete.Can you help ?


    DELETE orders.orderid, orders.Audit, customers1.Customerid
    FROM orders INNER JOIN customers1 ON orders.customerid = customers1.Customerid
    WHERE (((orders.orderid)<DMax([orderid],[orders],[Audit]=True)) AND ((orders.Audit)=True) AND ((customers1.Customerid) In (118,119,120,121,124,960,1008,402)));

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

    Re: Delete query (Access 2000)

    It's always difficult to create a delete query based on more than one table. Why do you need the Customers1 table in the query? You could put the condition on the CustomerID field from Orders instead of on the CustomerID field from Customers1.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query (Access 2000)

    Thank you for the reply.I get now the message enter parameter value.I am sending you the query, maybe you could find some other way ? In this case i want to delete all the orders having Audit = true and Customerid = 124
    Attached Files Attached Files

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

    Re: Delete query (Access 2000)

    All arguments to DMax are strings. The SQL should be

    DELETE orders.orderid, orders.Audit, orders.customerid
    FROM orders
    WHERE (((orders.orderid)<DMax("[orderid]","[orders]","[Audit]=True")) AND ((orders.Audit)=True) AND ((orders.customerid)=124));

    Since you have carefully supplied a table in which none of the records satisfy these conditions, no records will be deleted.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query (Access 2000)

    It is great ! I get the results required and the code runs and flies. Thank you very much !!

Posting Permissions

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