Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sql broken down (Access 2000)

    SQL broken down


    I have an sql broken down into parts,serving for other purposes.
    The parts of the query work fine except for two:
    sqlSmall and SQLSuppliers.
    It means that the SQL works fine in the following way :
    strBas = strHinge & strInvoice & strInvoiceDate
    But if i want to add sqlsmall or sqlsuppliers or both
    i receive errors.
    Can somebody help me? In order to be clear i will have to set down the whole lengthy function which is :

    Public Function Curves()
    Dim strBas As String
    Dim strHinge As String
    Dim strInvoiceDate
    Dim strBasHaving
    Dim strInvoice
    Dim strSmall
    Dim strSupplier as string

    strHinge = "SELECT DISTINCTROW Format([invoicedate],""yy-mm"") AS [Month], [Order Details].OrderID, [Order Details].ProductID, Products.grade, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.size, Sum([Order Details].liters) AS Liters, orders.paymentid, orders.invoicedate, customers.afid, customers.Customerid, Suppliers.Supplierid FROM (Suppliers INNER JOIN Products ON Suppliers.Supplierid = Products.supplierid) INNER JOIN ((customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN [Order Details] ON orders.orderid = [Order Details].OrderID) ON Products.Productid = [Order Details].ProductID GROUP BY Format([invoicedate],""yy-mm""), [Order Details].OrderID, [Order Details].ProductID, Products.grade, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.size, orders.paymentid, orders.invoicedate, customers.afid, customers.Customerid, Suppliers.Supplierid "
    strInvoice = "HAVING (((orders.paymentid)>0) "
    strInvoiceDate = "AND ((orders.invoicedate)>#1/1/2001#))"
    strSmall = "AND(( products.size) <6))"
    strsupplier = "AND(( suppliers.supplierid)=1))"


    strBas = strHinge & strInvoice & strInvoiceDate


    Curves = strBas
    End Function


    The working query in the grid is as follows


    SELECT DISTINCTROW Format([invoicedate],"yy-mm") AS [Month], [Order Details].OrderID, [Order Details].ProductID, Products.grade, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.size, Sum([Order Details].liters) AS Liters, orders.paymentid, orders.invoicedate, customers.afid, customers.Customerid, Suppliers.Supplierid
    FROM (Suppliers INNER JOIN Products ON Suppliers.Supplierid = Products.supplierid) INNER JOIN ((customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN [Order Details] ON orders.orderid = [Order Details].OrderID) ON Products.Productid = [Order Details].ProductID
    GROUP BY Format([invoicedate],"yy-mm"), [Order Details].OrderID, [Order Details].ProductID, Products.grade, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.size, orders.paymentid, orders.invoicedate, customers.afid, customers.Customerid, Suppliers.Supplierid
    HAVING (((orders.paymentid)>0) AND ((orders.invoicedate)>#1/1/2001#))
    ORDER BY [Order Details].OrderID;

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sql broken down (Access 2000)

    Posting the SQL and code is a good first step, but what kind of errors are you getting, error messages, incorrect results or what? We need specifics about error messages oe what is being returned erroneously.
    Charlotte

Posting Permissions

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