Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append error (Access 2000)

    I get an syntax error in my attempt to build a code that appends only that last row of orders from the table orders2, which has the field Suborder = True. In the table orders2 there may be also other fields with Yes= 0, but i want to update only that row which has the latest Suborder = True.

    I have done the following function :
    Dim StrSQL As String
    StrSQL = "INSERT INTO Orders1 " & _
    "SELECT * " & _
    " FROM Orders2 As o1 WHERE NOT EXISTS (SELECT * FROM Orders1 WHERE " & _
    "OrderID=o1.OrderID And Max(Suborder) = True))"
    CurrentDb.Execute StrSQL

    Am i right to use the Max and if so, where is my mistake ?
    Thank you in advance for the help

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append error (Access 2000)

    Keks,
    Use the Query design view to create your append query....drag all the requested fields onto the grid, (make sure you select append query), sort descending by your field that identifies the "last row", and then view the SQL. IN the select line, before your tbl, you need:

    SELECT TOP 1 ......
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append error (Access 2000)

    Thank you for your reply.Unfortnately your proposal does not solve my problem.I want to add only the last order and not all the orders.
    I have a function that works excellent and attaches all the records from the table which have a yes/No field = Yes.It is the following :
    Public Function Dummy()
    Dim StrSQL As String
    StrSQL = "INSERT INTO Orders1 " & _
    "SELECT * " & _
    " FROM Orders2 As o1 WHERE NOT EXISTS (SELECT * FROM Orders1 WHERE " & _
    "OrderID=o1.OrderID And Suborder = True)"
    CurrentDb.Execute StrSQL
    End Function
    In order not to append all the orders having a field suborder set to Yes,but only the last one, i have tried to amend the above function as follows:
    Public Function Dummy1()
    Dim StrSQL As String
    StrSQL = "INSERT INTO Orders1 " & _
    "SELECT * " & _
    " FROM Orders2 As o1 WHERE NOT EXISTS (SELECT * FROM Orders1 WHERE " & _
    "(SELECT Max (OrderID=o1.OrderID)FROM 01 And Suborder = True)"
    CurrentDb.Execute StrSQL
    End Function
    But this function has a syntax error, and besides i am not sure whether i am on the right track.
    I will be grateful for any help in this respect

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

    Re: Append error (Access 2000)

    Writing code does not mean that you can insert a function such as Max arbitrarily in your code and expect it to work. The best way to do this is, as NYIntensity indicated, to create the query in design view, and test it until it works, and only then look at the SQL view. Trying to write SQL directly is possible, but it is almost impossible to debug if it doesn't do what you want.

    Try this:

    strSQL = "INSERT INTO Orders1 SELECT * FROM Orders2 " & _
    "WHERE Orders2.OrderID=(SELECT Max(Orders2.OrderID) " & _
    "FROM Orders2 LEFT JOIN Orders1 ON Orders2.OrderID = Orders1.OrderID " & _
    "WHERE Orders1.OrderID Is Null AND Orders2.SubOrder=True)"

    I didn't create this SQL directly, I created a query in design view to get the highest OrderID in Orders2 for which there is no corresponding record in Orders1 and for which SubOrder is True. I then looked at the SQL and used this as criteria in an append query, again in design view. Finally, I looked at the SQL of this query, removed superfluous parentheses and divided it into lines of reasonable length for posting.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append error (Access 2000)

    Dear Hans and MyIntensity

    I want to thank you for your suggestions and advices.I have copied your sql proposal and it meets my needs and requirements.It was too tough for me to do it alone and for me your advices were great.
    Regards
    Keks

Posting Permissions

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