Lots of If..Then's 2

[jreynold]

I'm writing a sub to filter the results on a form. Right now I have two filters, but would like to add more and am unsure of the best way to continue. I want to add combo boxes for 5 more filters. This is going to turn into a lot of nested if statements if I continue going like I am.

StrFilterSupervisor and StrFilterClient are strings returned from a combo box selection.

Right now I have:

Private Sub AnyFilterChange()
Dim SetFilter As String
If (StrFilterClient = "" And StrFilterSupervisor = "") Then
DoCmd.ShowAllRecords
Else
If StrFilterClient = "" Then
SetFilter = StrFilterSupervisor
Else
If StrFilterSupervisor = "" Then
SetFilter = StrFilterClient
Else
SetFilter = StrFilterClient & " AND " & StrFilterSupervisor
End If
End If
DoCmd.ApplyFilter , SetFilter
End If
End Sub

 

[pbaldy]

You're sort of waiting until you know the whole thing and building the string then. A more flexible way is to build it as you go. One way:

strCriteria = "1 = 1"

If <your first test> Then
strCriteria = strCriteria & " AND blah blah"
End If

If <your second test> Then
strCriteria = strCriteria & " AND blah blah"
End If

and so on.

Paul
MS Access MVP 2007/2008

 

[jreynold]

Hmmm... Thanks Paul. Your setup makes good sense and is set up much more logically, however:

If the initial value of StrCriteria is "" rather than "1 = 1" and the rest of the tests add to it, it results in a string that starts with " AND ".

I guess I could do an if then at the end of the statement building block that would check the first five letters of StrCriteria and cut them off if they equaled " AND ".

Again thanks, I think your way of doing it will be much easier to code and more importantly much easier to decipher when I look at the code again in 6 months!

 

[MajP]

My technique is to give each combo its own string and then put them all together.

Here is an example with three multiselect listboxes.

Private Sub cmdClose_Click()
On Error GoTo errLabel

Dim strGrade As String
Dim strSOL As String
Dim strSOL_Status As String
Dim strWhere As String
Dim varItem As Variant

For Each varItem In Me.lstGrade.ItemsSelected
  strGrade = strGrade & "GradeSort = " & Me.lstGrade.ItemData(varItem) & " Or "
Next varItem
If Not strGrade = "" Then
  strGrade = "(" & Left(strGrade, Len(strGrade) - 3) & ") AND "
End If

For Each varItem In Me.lstSOL.ItemsSelected
  strSOL = strSOL & "intSOLID = " & Me.lstSOL.ItemData(varItem) & " Or "
Next varItem
If Not strSOL = "" Then
  strSOL = "(" & Left(strSOL, Len(strSOL) - 3) & ") AND "
End If

For Each varItem In Me.lstSOL_Status.ItemsSelected
  strSOL_Status = strSOL_Status & "SOL_Credit = '" & Me.lstSOL_Status.ItemData(varItem) & "' Or "
Next varItem
If Not strSOL_Status = "" Then
  strSOL_Status = "(" & Left(strSOL_Status, Len(strSOL_Status) - 3) & ") AND "
End If

strWhere = strGrade + " " + strSOL + " " + strSOL_Status
strWhere = Trim(strWhere)

If Not strWhere = "" Then
  strWhere = Left(strWhere, Len(strWhere) - 3)
End If

glblStrWhere = strWhere
DoCmd.Close
ExitLabel:
    Exit Sub

errLabel:
    MsgBox Err.Description
    Resume ExitLabel
End Sub

 

[randy700]

If the initial value of StrCriteria is "" rather than "1 = 1" and the rest of the tests add to it, it results in a string that starts with " AND ".

Which is exactly the reason he started with "1 = 1".


Randy

 

[jreynold]

Hehe, okay, so I'm new at this... .

What about all of the records where 1 = 2 ? Ha!

Thanks everyone for all the help and Randy for clearing the fog there for me!