Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not In in Row source (Access 2000)

    I have a constant called StrNotIn and it ususally works but not in the case i have.

    My ListBox is filled properly when i have the RowSource without the constant:

    Public Function Retrieval()
    Dim LO As String
    LO = "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) Not In

    (9000,9001,2700,2701,6300,6301,4000,4001,5000,5001 ,7700,7701,221,118,119,120,121,122,123,124,960))) Or

    (((orders.paymentid)=0))" & _
    " ORDER BY orders.orderdate"
    Forms!FOrderInformation!ListOfOrders.My.RowSource = LO
    End Function



    But if i replace the constant, the list box is empty:



    Public Const strNotIn = " Not In (221,118,119,120,121,122,123,124,960,992,1008,402) )"


    Public Function Retrieval()

    Dim LO As String
    LO = "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 & "))) Or (((orders.paymentid)=0))" & _
    " ORDER BY orders.orderdate"
    Forms!FOrderInformation!ListOfOrders.My.RowSource = LO
    End Function

    Is it the constant that gives me the headchaes, or there may be some other reason?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Not In in Row source (Access 2000)

    In the WHERE clause I think you will find that the number of ( don't balance the number of ).

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In in Row source (Access 2000)

    could you please explain.I could not understand your sentence

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Not In in Row source (Access 2000)

    Public Const strNotIn = " Not In (221,118,119,120,121,122,123,124,960,992,1008,402) )"


    Public Function Retrieval()

    Dim LO As String
    LO = "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 & "))) Or (((orders.paymentid)=0))"

    If you count the number of left round brackets "(", they do not match the number of right round brackets ")".

    Put a breakpoint on the LO = ... command and see what it contains in the immediate code window. If you then take a copy of the contents of the LO and put it into the SQL of a new query you should see what the problem is when you try to run the query.

    Show the contents of the LO in a response if you are still havings problems.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In in Row source (Access 2000)

    Thank you for your reply.To my regret i cannot solve the problem.It it is very difficult for me.
    In my attachment you can see both variants, with a constant and without constant.With the second alternative
    the list box is popultaed, but again it seems that the restriction Not In is not working.Maybe the problem is with the
    relations ?

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

    Re: Not In in Row source (Access 2000)

    1. Insert a line

    Debug.Print LO

    in both functions in the module, between the instruction LO = ... and the instruction Forms!Form1!ListOrders.RowSource = LO. Open the form and click both buttons, then switch to the Visual Basic Editor and activate the Immediate window (Ctrl+G).
    Compare the two strings. You will see that the first one contains too many closing parentheses ")".

    2. You have two conditions in the WHERE part of the SQL string, with Or between them. Or means that all records are returned that satisfy the first condition, or the second, or both. So records whose CustomerID is 1 or 2 can still be returned, if their PaymentID is 0. If you want to return records that satisfy both conditions, you should use And instead of Or.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In in Row source (Access 2000)

    Thank you for your reply.I have corrected the Or with And and i have the right dislay.As to the first function with the
    constant, i have removed all the parentheses and in spite of that the screen remains blank, as you can see in
    the attachment.I will be very grteful for your help

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

    Re: Not In in Row source (Access 2000)

    1. I didn't say that you should remove ALL parentheses! You need them in the FROM part of the SQL string.
    2. The constant strNotIn still contains one closing parenthesis ")" too many.
    3. The database you attached doesn't contain the table Affiliates, so the SQL will not be valid.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In in Row source (Access 2000)

    Thank you !! Following your recommendations all is OK now .

Posting Permissions

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