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 (Access 2000)

    Can i simplify a function where only one line is changed, i.e. AND ((workers.cid). Ususally i give this line a value based on the category chosen in the
    main form. For example if i choose Case 1, then the Record Source will be for BaseCrackers.This is a good and solid function,i just want to ask is there any way

    to furrther imrpove and simplify it.





    Public Function customersOnInvoice()

    Dim BaseCrackers As String
    BaseCrackers = " SELECT orders.paymentid, orders.invoicedate, customers.CompanyName, orders.customerid, workers.cid FROM affiliates INNER JOIN

    (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) ON workers.cid =

    customers.afid WHERE (((orders.paymentid)>0) AND ((orders.customerid) " & strNotIn & " AND ((workers.cid)=2)) ORDER BY orders.invoicedate"

    Dim BaseFitters As String
    BaseFitters = " SELECT orders.paymentid, orders.invoicedate, customers.CompanyName, orders.customerid, workers.cid FROM affiliates INNER JOIN

    (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) ON workers.cid =

    customers.afid WHERE (((orders.paymentid)>0) AND ((orders.customerid) " & strNotIn & " AND ((workers.cid)=3)) ORDER BY orders.invoicedate"

    Dim BasePlumbers As String
    BasePlumbers = " SELECT orders.paymentid, orders.invoicedate, customers.CompanyName, orders.customerid, workers.cid FROM affiliates INNER JOIN

    (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) ON workers.cid =

    customers.afid WHERE (((orders.paymentid)>0) AND ((orders.customerid) " & strNotIn & " AND ((workers.cid)=4)) ORDER BY orders.invoicedate"

    Dim BaseSetters As String
    BaseSetters = " SELECT orders.paymentid, orders.invoicedate, customers.CompanyName, orders.customerid, workers.cid FROM affiliates INNER JOIN

    (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) ON workers.cid =

    customers.afid WHERE (((orders.paymentid)>0) AND ((orders.customerid) " & strNotIn & " AND ((workers.cid)=5)) ORDER BY orders.invoicedate"
    ' ======================
    Select Case Forms![Clients![category]
    Dim baseCustomer As String
    Case 1
    baseCustomer = BaseCrackers
    Case 2
    baseCustomer = BaseFitters
    Case 3
    baseCustomer = BasePlumbers
    Case 4
    baseCustomer = BaseSetters
    End Select
    Forms![frmCustomerOffers].RecordSource = baseCustomer
    End Function

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simplifying sql (Access 2000)

    The first simplification you could try might look like this:

    (I haven

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simplifying sql (Access 2000)

    Another approach you could try might look like this:

    (I haven

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

    Re: simplifying sql (Access 2000)

    There are some programming rules of thumb that make it simpler to maintain code over time. One of those is that you declare *all* your variables at the top of the routine, not just before you use them. That ensures that you don't wind up with a variable that hasn't been declared yet when you try to use it and makes debugging easier.

    You're also concatenating a space at the beginning of your SQL string. Is there a reason for this? You're also referring to a "strNotIn" in your SQL, even though I don't see such a variable declared anywhere. Is this a module level variable? If so, it's customary to name it with an m on the fron (mstrNotIn) so that it's obvious it doesn't need to be declared in the routine. Aside from that, the only difference I saw in all those SQL strings was the Workers.cid. Why not do it like this?

    <pre>Public Function customersOnInvoice()
    Dim baseCustomer As String


    baseCustomer = "SELECT orders.paymentid, orders.invoicedate, " _
    & "customers.CompanyName, orders.customerid, workers.cid " _
    & "FROM affiliates INNER JOIN " _
    & "(customers INNER JOIN orders ON " _
    & "(customers.Customerid = orders.customerid) " _
    & "AND (customers.Customerid = orders.customerid)) ON workers.cid = " _
    & "customers.afid WHERE (((orders.paymentid)>0) AND ((orders.customerid) " _
    & strNotIn & " AND <font color=red>((workers.cid)=" & Forms![Clients]![Category] & ")) "</font color=red> _
    & "ORDER BY orders.invoicedate"


    Forms![frmCustomerOffers].RecordSource = baseCustomer
    End Function</pre>



    Note that if the category field from the form doesn't actually return the value of Workers.cid, you can use a choose or switch statement in there to replace the returned value with the one you need for the SQL string.
    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
  •