Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Thanked 0 Times in 0 Posts

    Syntax error in query expression (Access 2000)

    Happy New Year to all of you

    The following query is not accepted by Access and the mesage is " Syntax error in query expression".

    DELETE [order details].orderid, *FROM [order details]WHERE ((([order details].orderid) In (SELECT [order details].OrderID FROM [order details] LEFT JOIN [order

    details]1 ON [order details].OrderID = [order details1].OrderID WHERE (([order details1].OrderID) Is Null))))"

    May i ask for help ?? Actually i have rewritten the code from a similar query, which is accepted by Access.This query is the following:

    DELETE Orders.orderid, *
    FROM Orders
    WHERE (((Orders.orderid) In (SELECT Orders.OrderID FROM Orders LEFT JOIN Orders1 ON Orders.OrderID = Orders1.OrderID WHERE ((Orders1.OrderID) Is

    Perhaps somebody might help me and show me how can i delete my first query, containing 2 similar tables which are
    order details and order details1,
    and deleting those records that do no exist in the table order details1.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Mt Macedon, Victoria, Australia
    Thanked 45 Times in 44 Posts

    Re: Syntax error in query expression (Access 2000)

    I haven't check the rest of it, but this bit
    <pre>DELETE [order details].orderid, *FROM [order details]WHERE </pre>

    shoul be changed to
    <pre>DELETE [order details].* FROM [order details] WHERE etc</pre>

    When you delete, you delete whole records so you just need the *, and you need a space before FROM and before WHERE.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Syntax error in query expression (Access 2000)

    John Hutchison has already pointed out the two missing spaces in your SQL. There is another typo:

    ... LEFT JOIN [order details]1 ON ...

    should be

    ... LEFT JOIN [order details1] ON ...

    (the 1 should be inside the square brackets). Also, there is a double quote at the end of the SQL; if you are creating this in code, there should be a double quote at the beginning too; if you are creating it in query design view, there shouldn't be a double quote at the end.

    Note: Access always creates a lot of pairs of parentheses in SQL view of a query; if you are writing the SQL yourself, you don't need so many. The following SQL is valid:

    DELETE * FROM [order details] WHERE [order details].OrderID In (SELECT [order details].OrderID FROM [order details] LEFT JOIN [order details1] ON [order details].OrderID = [order details1].OrderID WHERE [order details1].OrderID Is Null)

    Note that the only parentheses used are around the nested SQL statement.

Posting Permissions

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