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

    Simplifying SQL code (Access 2000)

    I have a function for the Record source of several reports showing results depending on an Option Box in the Form.In my code i must always enumerate the full conditions of the option groupss, in my case strOffice and strSize.Is it possible to write the conditions in a separate code and to call them?I have tried, but i couldnt do it,i receive the message the variable is not available.I enumerate my full code in the OnOpen event


    Private Sub Report_Open(Cancel As Integer)
    Dim strBas As String
    Dim strOffice As String
    Dim strsize As String
    Dim strGroupByOrderBy As String
    strBas = " SELECT customers.CompanyName, Sum([order details].liters) AS SumOfliters " & _
    " FROM (affiliates INNER JOIN (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND

    (customers.Customerid = orders.customerid)) ON affiliates.afid = customers.afid) INNER JOIN (products INNER JOIN [order details] ON

    (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid = [order

    details].OrderID WHERE (((orders.paymentid) > 0) "

    strGroupByOrderBy = " GROUP BY customers.CompanyName ORDER BY customers.CompanyName"

    And below is the code, or the conditions which are one and the same for each report, but i have to repeat them
    an every report.:
    Select Case Forms![FBenchmark]![Gebinde]
    Case 1
    strsize = " And ((products.size) = <6)"
    Case 2
    strsize = " And ((products.size) =205)"
    Case 3
    strsize = " And ((products.size) = > 0.4)"
    End Function
    Select Case Forms![FBenchmark]![Office]
    Case 1
    strOffice = " And ((affiliates.afid) = 1)"
    Case 2
    strOffice = " And ((affiliates.afid) = 2)"
    Case 3
    strOffice = " And ((affiliates.afid) = 3)"
    Case 4
    strOffice= " And ((affiliates.afid) = 4)"
    Case 5
    strOffice = " And ((affiliates.afid) = 5)"
    Case 6
    strOffice =" And ((affiliates.afid) = 6)
    End Select

    Me.RecordSource = strBas & strOffice & strsize & strInvoiceDate & strGroupByOrderBy


    End Sub

    My question is, how can i refer to strOffice and strSize without writing each time one and the same condition?

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplifying SQL code (Access 2000)

    Surely you can write your condition checking in separate code. How did you do it, and what variable was not available?

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

    Re: Simplifying SQL code (Access 2000)

    You could create 2 hidden text boxes on your form, txtSize and txtAffiliate. For the AfterUpdate event of your Option Box, examine the choice in the Option Box and write the correct criteria to the proper Text Box. When you build your WHERE clause, have it refer to the values in the hidden Text Boxes.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Simplifying SQL code (Access 2000)

    Instead of this:

    Select Case Forms![FBenchmark]![Office]
    Case 1
    strOffice = " And ((affiliates.afid) = 1)"
    Case 2
    strOffice = " And ((affiliates.afid) = 2)"
    .
    .
    etc.

    Why not use " And affiliates.afid=Forms!Fbenchmark!Office"?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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