Syntax for filter with text field criteria

OK -- did more research and created a string to build the syntax to include all of the combo box criteria. But it still does not work. It is only building the string for the last combo box in the code. I commented out the last box and it selected data correctly for the last active combo box in the code etc. Would you please check out my code and tell me where my syntax is wrong? I am concerned about the first combo box check [Type_Inst]. I have a length check in there but maybe I should have an else in case the length of the string is 0 on the first 'If' statement.

Thanks, jketcher (code below)

Private Sub SelectList_Click()
On Error GoTo Err_SelectList_Click
Dim sFilter As String

'Filter type of school

If Me.[Type_Inst] <> "All" Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = "[T_YPEINSTI] =" & Chr(34) & Me!Type_Inst & Chr(34)
End If


'If Type of school is Proprietary, filter on type of proprietary (art, business etc.)

If Me.[Type_Inst] = "3" Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = "[T_YPEPROP] =" & Chr(34) & Me.Proprietary_Code & Chr(34)
End If

'Filter for length of Program
If Len(sFilter) > 0 Then sFilter = sFilter & "AND"
sFilter = "[L_ENPROG] =" & Chr(34) & Me.Program_Len & Chr(34)


'Filter for Traditional Total Enrollment

If Me.[Total_Students] = "All" Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = "[Und_Grad] > 0"
End If


If Me.[Total_Students] = "1-500" Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = "[Und_Grad] BETWEEN 1 AND 500"
End If


If Me.[Total_Students] = "501-1000" Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = "[Und_Grad] BETWEEN 501 AND 1000"
End If

If Me.[Total_Students] = "1001-2000" Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = "[Und_Grad] BETWEEN 1001 AND 2000"
End If


If Me.[Total_Students] = "2001-5000" Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = "[Und_Grad] BETWEEN 501 AND 1000"
End If


If Me.[Total_Students] = ">5000" Then
If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
sFilter = "[Und_Grad] > 5000"
End If


If Len(sFilter) > 0 Then
Me.[Marketing_subform].Form.Filter = sFilter
Me.[Marketing subform].Form.FilterOn = True
End If


Exit_WhereButton_Click:
Exit Sub
Err_SelectList_Click:
MsgBox Err.Description
Resume Exit_WhereButton_Click

End Sub
 
In almost all of the sections you are overwriting your :

sFilter = sFilter & ...

because you have put your IF's on ONE line instead using the two line method with an ELSE. :


Code: 
Private Sub SelectList_Click()
On Error GoTo Err_SelectList_Click
Dim sFilter As String

'Filter type of school

If Me.[Type_Inst] <> "All" Then
If Len(sFilter) > 0 Then 
   sFilter = sFilter & " AND "
[COLOR=red][B]ELSE[/B][/COLOR]
   sFilter = "[T_YPEINSTI] =" & Chr(34) & Me!Type_Inst & Chr(34)
End If


'If Type of school is Proprietary, filter on type of proprietary (art, business etc.)

If Me.[Type_Inst] = "3" Then
If Len(sFilter) > 0 Then 
   sFilter = sFilter & " AND "
[COLOR=red][B]ELSE[/B][/COLOR]
   sFilter = "[T_YPEPROP] =" & Chr(34) & Me.Proprietary_Code & Chr(34)
End If

'Filter for length of Program
If Len(sFilter) > 0 Then 
   sFilter = sFilter & "AND"
[COLOR=red][B]ELSE[/B][/COLOR]
   sFilter = "[L_ENPROG] =" & Chr(34) & Me.Program_Len & Chr(34)


'Filter for Traditional Total Enrollment

If Me.[Total_Students] = "All" Then
If Len(sFilter) > 0 Then 
   sFilter = sFilter & " AND "
[COLOR=red][B]ELSE[/B][/COLOR]
   sFilter = "[Und_Grad] > 0"
End If


If Me.[Total_Students] = "1-500" Then
If Len(sFilter) > 0 Then 
   sFilter = sFilter & " AND "
[COLOR=red][B]ELSE[/B][/COLOR]
   sFilter = "[Und_Grad] BETWEEN 1 AND 500"
End If


If Me.[Total_Students] = "501-1000" Then
If Len(sFilter) > 0 Then 
   sFilter = sFilter & " AND "
[COLOR=red][B]ELSE[/B][/COLOR]
   sFilter = "[Und_Grad] BETWEEN 501 AND 1000"
End If

If Me.[Total_Students] = "1001-2000" Then
If Len(sFilter) > 0 Then 
   sFilter = sFilter & " AND "
[COLOR=red][B]ELSE[/B][/COLOR]
   sFilter = "[Und_Grad] BETWEEN 1001 AND 2000"
End If


If Me.[Total_Students] = "2001-5000" Then
If Len(sFilter) > 0 Then 
   sFilter = sFilter & " AND "
[COLOR=red][B]ELSE[/B][/COLOR]
   sFilter = "[Und_Grad] BETWEEN 501 AND 1000"
End If


If Me.[Total_Students] = ">5000" Then
If Len(sFilter) > 0 Then 
   sFilter = sFilter & " AND "
[COLOR=red][B]ELSE[/B][/COLOR]
   sFilter = "[Und_Grad] > 5000"
End If


If Len(sFilter) > 0 Then
Me.[Marketing_subform].Form.Filter = sFilter
Me.[Marketing subform].Form.FilterOn = True
End If


Exit_WhereButton_Click:
Exit Sub
Err_SelectList_Click:
MsgBox Err.Description
Resume Exit_WhereButton_Click

End Sub
 
Is that exactly how I should code it? It seems that if for example the first combo box starts building the filter, the second one would find the length of the filter to be >0 and apply the true condition:

'Filter for length of Program
If Len(sFilter) > 0 Then
sFilter = sFilter & "AND"
*ELSE*
sFilter = "[L_ENPROG] =" & Chr(34) & Me.Program_Len & Chr(34)

Did you mean for me to add the length of program criteria, like for the Else statment? I will try this and am hopeful but it seems there is still something missing.
 
jketcher said:
Is that exactly how I should code it? It seems that if for example the first combo box starts building the filter, the second one would find the length of the filter to be >0 and apply the true condition:

'Filter for length of Program
If Len(sFilter) > 0 Then
sFilter = sFilter & "AND"
*ELSE*
sFilter = "[L_ENPROG] =" & Chr(34) & Me.Program_Len & Chr(34)

Did you mean for me to add the length of program criteria, like for the Else statment? I will try this and am hopeful but it seems there is still something missing.
Click to expand...

I see I missed something originally. You would need to do:
Code: 
[FONT=Consolas][SIZE=3]'Filter for length of Program[/SIZE][/FONT]
[FONT=Consolas][SIZE=3]If Len(sFilter) > 0 Then [/SIZE][/FONT]
[SIZE=3][FONT=Consolas]   sFilter = sFilter & [COLOR=red][B]"AND [L_ENPROG] =" & Chr(34) & Me.Program_Len & Chr(34)[/B][/COLOR][/FONT][/SIZE]
[FONT=Consolas][SIZE=3]ELSE[/SIZE][/FONT]
[SIZE=3][FONT=Consolas]   sFilter = "[L_ENPROG] =" & Chr(34) & Me.Program_Len & Chr(34)[/FONT][/SIZE]
[FONT=Consolas][SIZE=3]End If
[/SIZE][/FONT]

For each of those you would need to change it so that if the filter has something it appends the next part like the red and if it is zero length then it is just as if it started with this.
 
You must log in or register to reply here.

Similar threads

M
Dlookup frustration, multiple criteria
Replies
11
Views
1,336
arnelgp
arnelgp
J
Textbox as source criteria for multiple queries
Replies
10
Views
426
JPR
J
S
Format number with condition
Replies
3
Views
560
CJ_London
C
A
Solved Connection String mysterious Trim
Replies
3
Views
886
cheekybuddha
cheekybuddha
H
Solved Search across multiple fields
Replies
7
Views
1,377
Harry Paraskeva
H

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom