Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    syntax error (Access 2000)

    I came accross difficulties with replacing a constant for date in the where clause and receive the message syntax error.

    " WHERE (((orders.orderdate)>#1/1/2004#) AND ((Customers.size)=1) AND ((Customers.Customerid)=118))"
    my constant is :

    Public Const CnstStock As Date = #2/2/2004#
    after replacing the constant i receive the error :

    " WHERE (((orders.orderdate)> > #" & Format(CnstStock, "mm/dd/yyyy") & "#" ) AND ((Customers.size)=1) AND ((Customers.Customerid)=118))"

    Something is wrong with the above code and i will be grateful
    to receive some advice

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: syntax error (Access 2000)

    I believe you are ending up with duplicate effort - I think you can leave out the # characters in your WHERE clause and not use the Format statement as you have defined the Public Constant CnstStock as a date type already.
    Wendell

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

    Re: syntax error (Access 2000)

    You have an extra > character, and the quotes aren't matched correctly. Moreover, although Access creates enormous amounts of parentheses ( and ) around the where-conditions, you don't need to copy them in your own SQL.

    " WHERE orders.orderdate > #" & Format(CnstStock, "mm/dd/yyyy") & "# AND Customers.size=1 AND Customers.Customerid=118"

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax error (Access 2000)

    Thank you so much for your advice.I receive now a message "missing operator , ]
    or item in the query". I have seen that it is not due to the constant,but somehow
    when i replace the constant i must remove some bracket or so.I have made a lot of trials, but couldnt pinpoint what i have more or less with the brackets.
    Could you have a look at my record source? With basworking i have named
    the string that is working and with bas the string that shows the defect after
    replacement

    best regards



    Public Const CnstStock As Date = #1/1/2004#

    basworking = " SELECT [order details].ProductID, products.grade, products.size, orders.orderid, [order details].cartons, [order details].Quantity, orders.orderdate, Customers.afid, Customers.Customerid" & _
    " FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid " & _
    " WHERE (((orders.orderdate)>#1/1/2004#) AND ((Customers.afid)=1) AND ((Customers.Customerid)=118))"

    bas = " SELECT [order details].ProductID, products.grade, products.size, orders.orderid, [order details].cartons, [order details].Quantity, orders.orderdate, Customers.afid, Customers.Customerid" & _
    " FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid " & _
    " WHERE (((orders.orderdate)> #" & Format(CnstStock, "mm/dd/yyyy") & "# AND ((Customers.afid)=1) AND ((Customers.Customerid)=118))"

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

    Re: syntax error (Access 2000)

    As I wrote in my previous reply, you don't need all those ( and ) in the Where condition. They can easily trip you up; if you count carefully, you will see that you have an extra ( without ). Here is the version without all those parentheses:

    ...
    " WHERE orders.orderdate> #" & Format(cnststock, "mm/dd/yyyy") & "# AND Customers.afid=1 AND Customers.Customerid=118"

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax error (Access 2000)

    Dear Hans,

    I somehow get the error syntax error even though i have simplified my code
    in order to find out where the error lies.Is it possible in the declaration of the constant?

    Dim OhMy As Date
    OhMy = #1/1/2004#

    bas = " SELECT [order details].ProductID, products.grade, products.size, orders.orderid, [order details].cartons, [order details].Quantity, orders.orderdate, Customers.afid, Customers.Customerid" & _
    " FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid " & _
    " WHERE orders.orderdate > #" & Format(OhMy, "mm/dd/yyyy") & "# "

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

    Re: syntax error (Access 2000)

    What is the exact error you get now, and where? If I put this code in the Northwind database, and remove the fields that don't exist there, such as afid, from the SQL string, it works fine. I can open a recordset from it.

Posting Permissions

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