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

    Error with Constant (Access 2000)

    I have a RowSource that functions very good without the constant StrIn like that :

    strBas = " SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid " & _
    " FROM orders INNER JOIN customers ON orders.customerid = customers.Customerid " & _
    " WHERE (((orders.orderdate)>" & CnstYear & ") AND ((orders.customerid) In (118,119,120,121,124,950,992,1008)))"


    I want however to introduce a constant like that:

    Public Const strIn = " In (118,119,120,121,122,123,124,960))"

    In substituting the constant in my RowSource i obtain blank fields and my list box remains blank.It seems that I have an error
    either in the constant or in the code itself,may be some brackets more or less,i cannot find out the reason.
    Can somebody help me? The code under question that i want make working is the following

    strBas = " SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid " & _
    " FROM orders INNER JOIN customers ON orders.customerid = customers.Customerid " & _
    " WHERE (((orders.orderdate)>" & CnstYear & ") AND ((orders.customerid) " & strIn & " )))"

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

    Re: Error with Constant (Access 2000)

    There are too many closing parentheses in your code. Try the following:

    Public Const strIn = " In (118,119,120,121,122,123,124,960)"
    (one closing parenthesis instead of two)

    strBas = " SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid " & _
    " FROM orders INNER JOIN customers ON orders.customerid = customers.Customerid " & _
    " WHERE (((orders.orderdate)>" & CnstYear & ") AND ((orders.customerid) " & strIn & " ))"
    (two closing parentheses instead of three)

    or even

    strBas = " SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid " & _
    " FROM orders INNER JOIN customers ON orders.customerid = customers.Customerid " & _
    " WHERE orders.orderdate>" & CnstYear & " AND orders.customerid " & strIn
    (no parentheses at all)

    Note: if CnstYear is a number like 1999 and OrderDate is a Date/Time field, the criterion on OrderDate won't work as expected.

Posting Permissions

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