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

    sql in record source (Access 2000)

    Thanks to the help of two people i have tried to constrcut an sql clause for the RecordSource of any report.But i still cannot reach a satisfactory result.In this report i want to filter the size. The size is a control on the report. On the form i have an option group called Gebinde with three option called small pack, drums and all.Depending on the choise in the form i want to filter the report
    to get only products with the size i want.


    In the OnOpen event of my report i have the following code

    Dim strBas As String
    Dim Size As String

    SELECT DISTINCTROW Orders.paymentid, [order details].liters, products.size, customers.afid
    FROM customers INNER JOIN (Orders INNER JOIN (products INNER JOIN [order details] ON products.Productid = [order details].ProductID) ON Orders.OrderID = [order details].OrderID) ON customers.CustomerID = Orders.customerid
    WHERE (((Orders.paymentid)>0));

    StrSize = GetSize()
    Me.RecordSource = StrBas & strSize
    End Function

    I want to give the StrSize in my function the meaning that i will get from the function GetSize.But my code is not accepted and
    StrSize is not accepted.May i have some help? .I am nut sure whether what i am doing is proper at all,so may there there is some other solution to my problem.
    Below is my GetSize function:

    Public Function GetSize()
    Dim Gebinde As Control
    Set Gebinde = Forms![FBenchmark]![Gebinde]
    Dim strSmall As String
    Dim StrDrums As String
    Dim strWhere As String
    strSmall = " size < " & 6
    StrDrums = " size > " & 180
    If Gebinde = 1 Then
    strWhere = ""
    ElseIf Gebinde = 2 Then
    strWhere = strSmall
    ElseIf Gebinde = 3 Then
    strWhere = StrDrums
    End If
    GetSize = strWhere
    End Function

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql in record source (Access 2000)

    Use this code :
    <pre>Dim strBas As String
    Dim Size As String

    strbas = "SELECT DISTINCTROW Orders.paymentid, [order details].liters, products.size, " & _
    "customers.afid FROM customers INNER JOIN (Orders INNER JOIN " & _
    "(products INNER JOIN [order details] ON products.Productid = " & _
    "[order details].ProductID) ON Orders.OrderID = [order details].OrderID) " & _
    "ON customers.CustomerID = Orders.customerid " & _
    "WHERE (((Orders.paymentid)>0))"
    Select Case Forms![FBenchmark]![Gebinde]
    Case 2
    strSize = " AND size < 6"
    Case 3
    strSize = " AND size > 180"
    Case Else
    strSize = ""
    End Select

    Me.RecordSource = StrBas & strSize
    End Function</pre>

    Francois

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank you

    Thank you very much indeed for your help. With your help i have managed to make my code work.I have followed yoiur advice and my working code like this:
    Dim strBas As String
    Dim Size As String

    strbas = "SELECT DISTINCTROW Orders.paymentid, [order details].liters, products.size, " & _
    "customers.afid FROM customers INNER JOIN (Orders INNER JOIN " & _
    "(products INNER JOIN [order details] ON products.Productid = " & _
    "[order details].ProductID) ON Orders.OrderID = [order details].OrderID) " & _
    "ON customers.CustomerID = Orders.customerid " & _
    "WHERE (((Orders.paymentid)>0))"
    Select Case Forms![FBenchmark]![Gebinde]
    Case 1
    strSize = "And ((products.size) < 6)"
    'Reports(StrReportName)![sizeheading].Caption = "small pack"
    Case 2
    strSize = "And ((products.size) = 205)"
    'Reports(StrReportName)![sizeheading].Caption = "Drums"
    Case 3
    strSize = "And ((products.size) > 0.4)"
    'Reports(StrReportName)![sizeheading].Caption = "All packs"
    Me.RecordSource = StrBas & strSize
    End Function

    So you have solved my question. i would like to ask you can i go a step further. It is because i have many reports in my database where i use the filters for the sizes and i have to write in each report the following:
    Select Case Forms![FBenchmark]![Gebinde]
    Case 1
    strSize = "And ((products.size) < 6)"
    'Reports(StrReportName)![sizeheading].Caption = "small pack"
    Case 2
    strSize = "And ((products.size) = 205)"
    'Reports(StrReportName)![sizeheading].Caption = "Drums"
    Case 3
    strSize = "And ((products.size) > 0.4)"
    'Reports(StrReportName)![sizeheading].Caption = "All packs"

    My wish is to construct a global code, and call ot for example GetSize(), to put it in the section Modules,
    and to call it whenever i want to filter this report.I know a way to call such function with
    GetSize(Me.name) but can i tied it up with the string StrSize ? I have tried it with several ways
    but i receive the message that the string strSize is not known.
    Please accept my best regards

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thank you

    You can create two functions in a module.
    <pre>Function GetSize() As String
    Select Case Forms![FBenchmark]![Gebinde]
    Case 1
    GetSize = "And ((products.size) < 6)"
    Case 2
    GetSize = "And ((products.size) = 205)"
    Case 3
    GetSize = "And ((products.size) > 0.4)"
    End Select
    End Function</pre>

    <pre>Function GetSizeHeading() As String
    Select Case Forms![FBenchmark]![Gebinde]
    Case 1
    GetSizeHeading = "small pack"
    Case 2
    GetSizeHeading = "Drums"
    Case 3
    GetSizeHeading = "All packs"
    End Select
    End Function</pre>

    In the report on open event enter :
    <pre>Dim strBas As String
    strbas = "SELECT DISTINCTROW Orders.paymentid, [order details].liters, products.size, " & _
    "customers.afid FROM customers INNER JOIN (Orders INNER JOIN " & _
    "(products INNER JOIN [order details] ON products.Productid = " & _
    "[order details].ProductID) ON Orders.OrderID = [order details].OrderID) " & _
    "ON customers.CustomerID = Orders.customerid " & _
    "WHERE (((Orders.paymentid)>0))"
    Me.RecordSource = StrBas & GetSize
    Me![sizeheading].Caption = GetSizeHeading</pre>

    Francois

Posting Permissions

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