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

    error end of statement (Access 2000)

    I am trying to build a function that refers to the table instead to the form. I think i should use the DLookup function, i do not know any other way.
    However, i receive the error "Expected : end of statement"
    Can i refer to a table at all in the function mentioned below and where might the error
    be in this function ?

    Public Function RemoveBefore()
    Dim SqlRemoveFromOrders As String
    SqlRemoveFromOrders = " DELETE DISTINCTROW orders.orderdate AS Expr1 " & _
    " FROM orders " & _
    " WHERE orders.orderdate < DLookUp("[CutDate]";"TblCutDate")"
    CurrentDb.Execute SqlRemoveFromOrders
    End Function

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

    Re: error end of statement (Access 2000)

    The first problem is that you have used quoted strings such as "TblCutDate" within the quoted string SqlRemoveFromOrders. This confuses the parser: where does the string end?

    To include a string within a string, you must enclose it in single quotes instead of double quotes, OR use double double quotes.

    You must use , not ; in DLookup.

    As John Hutchison remarked in <post#=379863>post 379863</post#>, a delete query always removes whole records, so you don't need to use "DELETE DISTINCTROW orders.orderdate AS Expr1", just "DELETE *" is enough:

    <code>SqlRemoveFromOrders = " DELETE * FROM orders " & _</code>
    <code>" WHERE orders.orderdate < DLookUp('CutDate','TblCutDate')"</code>

    Note: if TblCutDate contains more than one record, DLookup will return a value from an arbitrary record.

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

    Re: error end of statement (Access 2000)

    Thank you very much indeed for your wonderful answer.And also for this wonderful site !
    I have now my function in full swing!!
    About your advice as to use just delete instead of Delete Distinctriw ,i have done it with sucess
    for the table orders.Could you please have a loook at my code for the same puspose just for the
    other related table [order details]. How shall i begin, with delete * [orders]
    or [order details] in the first line ?.Because in that case i am using the Inner Join and i get confused.


    SqlRemoveFromOrderDetails = " DELETE DISTINCTROW [order details].*, orders.orderdate AS Expr1 " & _
    "FROM orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID " & _
    " WHERE orders.orderdate < DLookUp('CutDate','TblCutDate')"



    kind regards

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

    Re: error end of statement (Access 2000)

    The best way to do this is to make sure that the relationship between Orders and Order Details enforces referential integrity, and has cascading deletes set. (You can check this by double clicking the line between the tables in the Relationships window.) Cascading deletes means that if you delete records from Orders, the related records in Order Details will automatically be deleted too. Then, you only have to use the code you already had, to delete records from Orders that satisfy a certain condition.

Posting Permissions

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