Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    addition of where clause (Access 2000)

    The addition of an aditional where clause namely "And orders.Audit = -1" makes my sql invalid. Why is it so, since i see no mistake.Because the original sql, called LOOriginal works fine, it is only the amended sql called LO , where i have put the addition of "And orders.Audit = -1 " that gives a null results :

    LO = "SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid, orders.paymentid,orders.Audit, affiliates.afid" & _
    " FROM affiliates INNER JOIN (customers INNER JOIN orders ON (customers.Customerid=orders.customerid) AND (customers.Customerid=orders.customerid)) ON affiliates.afid=customers.afid " & _
    " WHERE (((orders.customerid) " & strNotIn & "))And (((orders.paymentid)= 0) And orders.Audit = -1))" & _
    " ORDER BY orders.orderdate"
    LOOroginal = "SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid, orders.paymentid, affiliates.afid" & _
    " FROM affiliates INNER JOIN (customers INNER JOIN orders ON (customers.Customerid=orders.customerid) AND (customers.Customerid=orders.customerid)) ON affiliates.afid=customers.afid " & _
    " WHERE (((orders.customerid) " & strNotIn & "))And (((orders.paymentid)= 0))" & _
    " ORDER BY orders.orderdate"

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

    Re: addition of where clause (Access 2000)

    You must keep track of the opening and closing parentheses ( and ) in the Where clause. They should balance, i.e. the number of ( should be the same as the number of ). If you count them in LO, you'll see that you have one ) too many.

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

    Re: addition of where clause (Access 2000)

    Addition: as I already recommended in <post:=567,289>post 567,289</post:>:
    <hr>General tip: try out SQL by designing a query first. When the query works to your satisfaction, look at its SQL (View | SQL) and use this to build your VBA code.<hr>
    That way you're less likely to make mistakes.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: addition of where clause (Access 2000)

    Thank you so much i have solved it !

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: addition of where clause (Access 2000)

    Or Vice Versa

    If you have a problem with an SQL string, use Debug.Print to capture the SQL. Then copy it from the Immediate Window and Paste it into SQL view of a new query.
    Try to switch to Design View or Datasheet View and it will often reveal the problem with the original SQL string.
    Regards
    John



Posting Permissions

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