Simplifying SQL code (Access 2000)

Topic

New Reply

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) = 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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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”?

Reply | Quote