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

    Syntax error in sql filter (Access 2000)

    I have the following working sql code:


    Dim BasProduct As String
    BasProduct = " SELECT products.Productid, products.grade, Sum([order details].liters) AS Liters, Year([invoicedate]) AS Expr1, Month([invoiceDate]) AS ContrMonth, orders.invoicedate, customers.Customerid, orders.paymentid, customers.afid, products.size " & _
    " FROM products INNER JOIN ((customers INNER JOIN orders ON customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) ON products.Productid = [order details].ProductID " & _
    " GROUP BY products.Productid, products.grade, Year([invoicedate]), Month([invoiceDate]), orders.invoicedate, customers.Customerid, orders.paymentid, customers.afid, products.size " & _
    " HAVING (orders.invoicedate) > #1/1/2001# And (orders.paymentid) >0"

    Also I have the following additional Having clause:
    Dim PrLondon As String
    PrLondon = "And (customers.afid) = 1"


    If I write Me.RecordSource = BasProduct & PrLondon,
    Then it is OK, I receive the results for London.
    However,if I write the filtering code :
    Me.filterOn = True
    Me.Filter = PrLondon

    Then I receive a message fro syntax error(missing operator) in query expression
    "And (customers.afid) = 1"


    May I ask for help ?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Syntax error in sql filter (Access 2000)

    You're missing a space between the end of the BasProduct string and the beginning of the PrLondon string, so it winds up reading:

    " HAVING (orders.invoicedate) > #1/1/2001# And (orders.paymentid) >0And (customers.afid) = 1"

    Add a space before the "And" in PrLondon and that should take care of it.
    Charlotte

Posting Permissions

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