Looking for better way to do this chain query (1 Viewer)

[jwillet1]

I have what I am going to call a chain query that works, however I believe there is a more concise and clear way to write it out.

Here is what I want and how it works.

The form where the query is essentially defaults to all records. There are three filter values that a user can choose from, vProgram, vVehicle, and vAttribute. At form open all records will be displayed, that is all program, vehicles and attributes. The query below does this correctly.

From the top down it will sort:
Program (all) -> Vehicle (all) -> Attribute (all)
Program (all) -> Vehicle (all) -> Attribute (single)

Program (single) -> Vehicle (all) -> Attribute (all)
Program (single) -> Vehicle (all) -> Attribute (single)

Program (single) -> Vehicle (single) -> Attribute (all)
Program (single) -> Vehicle (single) -> Attribute (single)

Is there a better way to do this?

If Me.cboFilterProgram = gciProgramAllID Then
        If Me.cboFilterAttribute = "All" Then
            Me.lstAppointments.RowSource = "SELECT tblPlan.PlanID, tblPlan.PlanVehicleID, tblPlan.PlanAttribute, " & _
            "DateValue([PlanStart]) AS [AS], DateValue([PlanEnd]) AS AE, tblPlan.PlanEngineer, tblPlan.PlanProgramNumberID, tblPlan.PlanStart " & _
            "FROM tblPlan " & _
            "ORDER BY PlanStart;"
        Else
            Me.lstAppointments.RowSource = "SELECT tblPlan.PlanID, tblPlan.PlanVehicleID, tblPlan.PlanAttribute, " & _
            "DateValue([PlanStart]) AS [AS], DateValue([PlanEnd]) AS AE, tblPlan.PlanEngineer, tblPlan.PlanProgramNumberID, tblPlan.PlanStart " & _
            "From tblPlan " & _
            "WHERE PlanAttribute = " & QUOTE & Me.cboFilterAttribute & QUOTE & "ORDER BY PlanStart;"
        End If
    Else
        If Me.cboFilterVehicle = "All" Then
            If Me.cboFilterAttribute = "All" Then
                Me.lstAppointments.RowSource = "SELECT tblPlan.PlanID, tblPlan.PlanVehicleID, tblPlan.PlanAttribute, " & _
                "DateValue([PlanStart]) AS [AS], DateValue([PlanEnd]) AS AE, tblPlan.PlanEngineer, tblPlan.PlanProgramNumberID, tblPlan.PlanStart " & _
                "From tblPlan " & _
                "WHERE PlanProgramNumberID = " & Me.cboFilterProgram & "ORDER BY PlanStart;"
            Else
                Me.lstAppointments.RowSource = "SELECT tblPlan.PlanID, tblPlan.PlanVehicleID, tblPlan.PlanAttribute, " & _
                "DateValue([PlanStart]) AS [AS], DateValue([PlanEnd]) AS AE, tblPlan.PlanEngineer, tblPlan.PlanProgramNumberID, tblPlan.PlanStart " & _
                "From tblPlan " & _
                "WHERE PlanProgramNumberID = " & Me.cboFilterProgram & " AND " & _
                "PlanAttribute = " & QUOTE & Me.cboFilterAttribute & QUOTE & "ORDER BY PlanStart;"
            End If
        Else
            If Me.cboFilterAttribute = "All" Then
                Me.lstAppointments.RowSource = "SELECT tblPlan.PlanID, tblPlan.PlanVehicleID, tblPlan.PlanAttribute, " & _
                "DateValue([PlanStart]) AS [AS], DateValue([PlanEnd]) AS AE, tblPlan.PlanEngineer, tblPlan.PlanProgramNumberID, tblPlan.PlanStart " & _
                "From tblPlan " & _
                "WHERE PlanProgramNumberID = " & Me.cboFilterProgram & " AND " & _
                "PlanVehicleID = " & QUOTE & Me.cboFilterVehicle & QUOTE & "ORDER BY PlanStart;"
            Else
                Me.lstAppointments.RowSource = "SELECT tblPlan.PlanID, tblPlan.PlanVehicleID, tblPlan.PlanAttribute, " & _
                "DateValue([PlanStart]) AS [AS], DateValue([PlanEnd]) AS AE, tblPlan.PlanEngineer, tblPlan.PlanProgramNumberID, tblPlan.PlanStart " & _
                "From tblPlan " & _
                "WHERE PlanProgramNumberID = " & Me.cboFilterProgram & " AND " & _
                "PlanVehicleID = " & QUOTE & Me.cboFilterVehicle & QUOTE & " AND " & _
                "PlanAttribute = " & QUOTE & Me.cboFilterAttribute & QUOTE & "ORDER BY PlanStart;"
            End If
        End If
    End If

Hopefully this is clear and I appreciate any and all help.

 

[vbaInet]

I've not looked at the detail in your code but just looked at the logic and you're doing it the wrong way round.

1. You have 2 main roots, Program(all) and Program(single) - look at the first 4 statements you wrote
2. Inside Program(all) you have one branch Vehicle(all). Inside Vehicle(all) you have two branches Attribute(all) and Attribute(single)
3. Inside Program(single) you have two branches Vehicle(all) and Vehicle(single). And so on..., you get the gist of it!

Translate this to IF ELSE blocks.

 

[Rx_]

You lost me, but that isn't that hard to do.
Is the intent to have multiple filters on the form from one recordset?
For example, after opening the recordset on a form, the filter can be applied to a field and reset the dataset.

Sometimes, reviewing some of the demo DB on this forum gives me an inspiration or a different point of view. Here are a couple that might help.

http://www.access-programmers.co.uk/forums/showthread.php?t=99777&highlight=filter&page=2
Look at # 9 and #23 for some filtering data in form ideas.

 

[MarkK]

All you need to construct is a WHERE clause. Each WHERE clause might have one or more criteria based on one or more controls on the form. The way I would approach this is with a property for each criteria, and a property for the Where clause itself, and a final property for the full SQL.

Consider code like this . . .

Private Const SQL_BASE = _
   "SELECT PlanID, PlanVehicleID, PlanAttribute, DateValue([PlanStart]) AS [AS], DateValue([PlanEnd]) AS AE, PlanEngineer, PlanProgramNumberID, PlanStart " & _
   "From tblPlan "

Property Get ProgramFilter As String
[COLOR="Green"]'   so if the control is null, the clause is simply absent and returns all records[/COLOR]
   If Not IsNull(Me.cboProgramFilter) Then ProgramFilter = "AND PlanProgramNumberID = '" & Me.cboFilterProgram & "' "
End Property

Property Get VehicleFilter As String
   If Not IsNull(Me.cboFilterVehicle) Then VehicleFilter = "AND PlanVehicleID = '" & Me.cboFilterVehicle & "' "
End Property

. . . and the third, PlanFilter, is an exercise, then you need a . . .

Property Get WhereClause As String
   Dim tmp as String
   tmp = Me.ProgramFilter & Me.VehicleFilter & Me.PlanFilter
[COLOR="Green"]   'if there is a where clause, it has a leading "AND ", which we drop here[/COLOR]
   If tmp <> "" then WhereClause = "WHERE " & Mid(tmp, 5)
End Property

Property Get SQL as String
[COLOR="Green"]   'This property pulls it all together, the SQL_BASE, the WhereClause and the Order By is explicit
   'each time you reference this property, the whole SQL Statement will be 
   'automatically rebuilt based on the current settings in the form controls.
[/COLOR]   SQL = SQL_BASE & Me.WhereClause & "ORDER BY PlanStart;"
End Property

So. Now. Anytime you consume the property SQL, the whole SQL statement is rebuilt from scratch, with very little code, and very easy to extend and understand. And then to make it work, maybe you do . . .

Private Sub cmdApplyFilter_Click()
[COLOR="Green"]   'see how this automatically rebuilds Me.SQL on each execution[/COLOR]
   Me.lstFiltered.RowSource = Me.SQL
End Sub

Hope this helps,

 

[jwillet1]

Thank you very much for the help, that is a nice elegant and readable method to solve my dilemma.

I do have a question, I have never used it before so what is the purpose of the "Property" function? Why use property instead of something like just a variable?

Forgive the question if the answer is obvious, I guess I am trying to figure out what property does different, so I might be able to use it in the future is the needs arise.