Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sql as record source (Access 2000)

    I am trying to replace a constant in an sql record source of a form but i am worng somewhere since i cannot obtain the results.I would be grateful for some help.In order to be helped i need to explain how i work
    Here is the valid sql clause that works and that displays the results:

    strbas = " SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid " & _
    " FROM customers INNER JOIN orders ON customers.Customerid = orders.customerid " & _
    " WHERE (((orders.orderdate)>#1/1/2002#) AND ((orders.customerid) In (118,119,120)))"

    Now i want to build a constant to replace the part In (118,119,120) . To thois purpose i have doen the following:

    Public Const StrIn = " In(118,119,120)"

    And then i tried to replace the constant in the following strbas1 clause:

    strbas1 = " SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid " & _
    " FROM customers INNER JOIN orders ON customers.Customerid = orders.customerid " & _
    " WHERE (((orders.orderdate)>#1/1/2002#) AND ((orders.customerid) " & StrIn & ")))"


    When i open the form with the record source strbas1 i receive blank fields, when i open the form with strbas then it is ok. it follows that i am wrong somewhere in my replacements.
    May i have some help?

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql as record source (Access 2000)

    So close

    Try making your StrIn "118,119,120"

    Your strbas1 should read:

    strbas1 = " SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid " & _
    " FROM customers INNER JOIN orders ON customers.Customerid = orders.customerid " & _
    " WHERE (((orders.orderdate)>#1/1/2002#) AND ((orders.customerid) In (" & StrIn & ")))"

    (You might need a semi-colon at the end)
    (Haven't had time to test this!!)
    HTH
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql as record source (Access 2000)

    you ended up with an extra closing bracket!

    Try

    strbas1 = " SELECT orders.orderid, orders.orderdate, customers.CompanyName, orders.customerid " & _
    " FROM customers INNER JOIN orders ON customers.Customerid = orders.customerid " & _
    " WHERE (((orders.orderdate)>#1/1/2002#) AND ((orders.customerid)" & StrIn & "))"

    HTH

    Peter

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql as record source (Access 2000)

    The surest way I've found to trouble shoot problems of this nature is to throw in a trusty Debug.Print statement printing your constructed SQL statement to the immediate window; you can then compare/contrast your constructed SQL statement with your known functional SQL statement. Teach a person to fish, and all that! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

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