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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 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.

  4. #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

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 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.

  6. #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
  •