Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Thanked 0 Times in 0 Posts

    wrong sql code (Access 2000)

    I have the following working and valid sql RowSource of a List :

    SELECT orders.orderid, orders.orderdate, orders.customerid, orders.AuftragNr" & _
    "FROM orders " & _
    " WHERE (((orders.orderdate)>#1/1/2001#)" * _
    " AND ((orders.customerid)=123)" & _
    "AND ((orders.AuftragNr)>0));"

    I want to break down the code so that i can choose between several possiblilites of Date and Customer as follows:

    dim strbas as string

    strBas = " SELECT orders.orderid, orders.orderdate, orders.customerid, orders.AuftragNr" & _
    "FROM orders

    Dim strOrderDate As String
    Dim strCustomer As String
    Dim strAuftrag As String

    strOrderDate = " WHERE (((orders.orderdate)>#1/1/2001#)"
    strCustomer = "AND ((orders.customerid)=123)"
    strAuftrag = "AND ((orders.AuftragNr)>0))"

    Then i build up the SQL code in the following way :

    Me![orderid].RowSource = strBas & strOrderDate & strCustomer & strAuftrag

    However the result is deplorable,i receive nothing.I suppose a wrong SQL structure.
    Can somebody help me?

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

    Re: wrong sql code (Access 2000)

    try this:

    strOrderDate = " WHERE (((orders.orderdate)>#1/1/2001#)"
    strCustomer = " AND ((orders.customerid)=123)"
    strAuftrag = " AND ((orders.AuftragNr)>0));"

    You were missing two spaces before the ANDs and the ending semi-colon.
    If it still does not work try putting in

    Debug.Print strBas & strOrderDate & strCustomer & strAuftrag

    Copy it from the Immediate Window into a new query (SQL view) and try to run it. This might show up any other problems.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    Star Lounger
    Join Date
    May 2001
    Ventura, California, USA
    Thanked 0 Times in 0 Posts

    Re: wrong sql code (Access 2000)

    I have a very similar problem, and am struggling with the answer.
    I am setting a textbox's Control Source to a SQL code, but the textbox shows either "#Name?" or the actual SQL string.

    Here's my code:
    Private Sub Form_Current()

    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim strSQL4 As String
    Dim strSQL5 As String
    Dim strSQL_Stmt As String

    strSQL1 = " SELECT [Order Details].UnitPrice FROM Products INNER JOIN"
    strSQL2 = " [Order Details] ON Products.ProductID = [Order Details].ProductID"
    strSQL3 = " WHERE ((([Order Details].OrderID)=[Forms]![Orders]![OrderID])"
    strSQL4 = " AND ((Products.ProductName)='Tofu'));"

    strSQL_Stmt = strSQL1 & strSQL2 & strSQL3 & strSQL4

    Me!txtUnitPrice_G = strSQL_Stmt

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: wrong sql code (Access 2000)

    That's because you can't set a Textbox's control source to a SQL string, and the SQL you listed looks more like the datasource for a form, not the SQL to return a single value for a textbox. You *can* use a SQL select statement as the rowsource for a listbox or combobox in Access, but that's an entirely different kind of thing. If you explain more about what you're trying to accomplish, I'm sure someone will be able to help you.

Posting Permissions

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