Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    wrong comcatenation of strings (Access 2000)

    Wrong Concatenation of strings

    I have one function for showing the available products in the main form. It works for individual branches, for example for branch1 the
    function works as follows:


    Public Function MainProductStrings()
    Dim city As Long
    city = Forms![FOrderInformation]![office] - 1
    Dim bas As String

    ' if i define only one branch, say branch 1 then it works:
    bas = " SELECT Products.Productid, Products.grade, Products.size, Products.pack, Products.branch1, Products.items1 FROM Products " & _
    " WHERE (((Products.branch1) > 0))" & _
    " ORDER BY Products.grade"

    Dim strDocName As String
    strDocName = "FOrderinformation"
    If IsOpen(strDocName) = True Then
    MainProductStrings = bas
    End If
    End Function


    however i wanted to replace the string bas with the more general
    bas = " SELECT Products.Productid, Products.grade, Products.size, Products.pack, products.branch" & city & ",products.items" & city & " FROM Products " & " WHERE (((products.branch" & city & " > 0)) " & _
    " ORDER BY Products.grade"

    which will cover all the branches in the tablke Products.These are branch1,branch2,branch3,.
    Even though i obtain no messges for error,the function seem not to work and i receive blank fields.Obviously something is wrong
    with my concatenations.
    Could sombody help me?



    Secondly, the foloiwing function shows a syntax error when i tried to simplify the sqlafid with
    AND ((affiliates.afid)" & city & "

    instead of the lengthy Select case i tried to replace it with the folowing code

    sqlafid = "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) AND ((affiliates.afid)" & city & ")) ORDER BY orders.orderdate"

    I have a syntax error with the above code.Can somebody help me with rectifying it?

    below is the previous code with the select case statement.

    Public Function customersOnOrder()
    Dim city As Long
    city = Forms![FOrderInformation]![office] - 1

    Dim strDocName As String
    strDocName = "FOrderinformation"
    Dim office As Control
    Set office = Forms![FOrderInformation]![office]
    Dim basCustomer As String
    basCustomer = "SELECT DISTINCT Customers.Customerid, Customers.CompanyName, orders.orderdate, orders.invoicedate, orders.orderid, orders.paymentid, Customers.afid FROM " & _
    "Customers INNER JOIN orders ON (Customers.Customerid = orders.customerid) " & _
    " AND (Customers.Customerid = orders.customerid) " & _
    "WHERE (((orders.orderid)>0) AND ((orders.paymentid)=0) AND ((orders.customerid) " & strNotIn & ""
    Dim sqlafid As String

    If IsOpen(strDocName) = True Then
    Select Case Forms![FOrderInformation]![office]
    Case 1
    sqlafid = "AND ((Customers.afid)=1))"
    Case 2
    sqlafid = "AND ((Customers.afid)=2))"
    Case 3
    'sqlafid = "AND ((Customers.afid)=3))"
    Case 4
    sqlafid = "AND ((Customers.afid)=4))"
    Case 5
    sqlafid = "AND ((Customers.afid)=5))"
    Case 6
    sqlafid = "AND ((Customers.afid)=6))"
    Case 7
    sqlafid = "AND ((Customers.afid)=7))"
    Case 8
    sqlafid = "AND ((Customers.afid)=8))"
    Case Else
    End Select
    Forms![frmCustomerOrders].RecordSource = basCustomer & sqlafid

    End If
    End Function

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

    Re: wrong comcatenation of strings (Access 2000)

    In the code for just one branch
    " WHERE (((Products.branch1) > 0))" & _
    You have a ) after the 1.
    In the more general code, this bracket is missing
    & " WHERE (((products.branch" & city & " > 0)) " & _

    Try & " WHERE (((products.branch" & city & ") > 0)) " & _
    Regards
    John



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

    Re: wrong comcatenation of strings (Access 2000)

    For your first question, try changing your bas command from:

    bas = " SELECT Products.Productid, Products.grade, Products.size, Products.pack, products.branch" & city & ",products.items" & city & " FROM Products " & " WHERE (((products.branch" & city & " > 0)) " & _
    " ORDER BY Products.grade"

    to:

    bas = " SELECT Products.Productid, Products.grade, Products.size, Products.pack, products.branch" & city & ",products.items" & city & " FROM Products " & " WHERE (((products.branch" & city & " ) > 0)) " & _
    " ORDER BY Products.grade"

    Note on the second line the addition of the right hand bracket just prior to the greater than sign.
    You also define City as Forms![FOrderInformation]![office] - 1, do you really mean to subtract one from the calculation?

    For your second question, I don

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

    Re: wrong comcatenation of strings (Access 2000)

    Re the second question.
    You seem to have a double join between customers and orders
    (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid))

    The first WHERE condition does not have a comparison value
    WHERE (((orders.customerid) " & strNotIn Should this be: WHERE (((orders.customerid)= " & strNotIn
    Same with the last condition
    AND ((affiliates.afid)" & city & ") Should this be AND ((affiliates.afid)=" & city & ")
    (This post edited by John to correct a mistake)
    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
  •