filter by multiple combo boxes

G

Gnerks

I have a form that has six combo boxes used for filtering information on the
form. They are (in order)
cboScope (Filter options include =ALL, AF, DRU, FOA, MAJCOM) ;
cboMAJCOM (Filter options include = ALL, AF, AETC, ACC, AFMC, …);
cboMRS (filter options include = ALL, 1 MRS, 2 MRS, 3 MRS, …);
cboSTUDY TYPE (filter options include = ALL, CMS, MAS, S/S, FACTORS, …);
cboORG (filter options include: ALL, 2T2, AFSPC, ASU, …);
cboSTATUS (filter options include: ALL, COMPLETE, COMPLETE – IN STAFFING,
PUBLISHED…)
I need to be able to allow the user to filter these combo boxes one at a
time, all at once, or by any combination. For example the user may decide to
filter the cboSCOPE to ‘AF’ and the filter cboMRS to ‘1 MRS’ .
I am not sure how to set my strWhere to allow for the filtering of multiple
combo boxes. I have reviewed Allen Browne’s Client Search information,
however, I remain confused.
Code looks like:
If Me.cboMAJCOM > "ALL" Then
strWhere = strWhere & "([Scope] <> 'ALL') AND "
ElseIf Me.cboMAJCOM = "ALL" Then
strWhere = strWhere & "([Scope] = 'ALL') "
End If

'Remove the ending AND
'See if the string has more than 5 characters (a trailing " AND ") to
remove

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'nothing in the string
MsgBox "No Criteria", vbInformation, "Nothing to do"
Else
strWhere = Left$(strWhere, lngLen)

'apply the string as the form's filter
Me.Filter = strWhere
Me.FilterOn = True
End If

Can you help?
 
G

Graham R Seach

The usual way of doing this is to create a procedure for building a dynamic
SQL statement on-the-run.

Private Sub BuildSQL()
Dim strSQL As String

If Len(Nz(Me!cboScope,"")) > 0 Then
If Me!cboScope <> "ALL" Then
strSQL = "somefield = '" & me!cboScope & "'"
End If
End If

If Len(Nz(Me!MAJCOM,"")) > 0 Then
If Me!MAJCOM <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me!MAJCOM & "'"
End If
End If

If Len(Nz(Me!cboMRS,"")) > 0 Then
If Me!cboMRS <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me!cboMRS & "'"
End If
End If

If Len(Nz(Me![cboSTUDY TYPE],"")) > 0 Then
If Me![cboSTUDY TYPE] <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me![cboSTUDY TYPE] & "'"
End If
End If

If Len(Nz(Me!cboORG,"")) > 0 Then
If Me!cboORG <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me!cboORG & "'"
End If
End If

If Len(Nz(Me!cboSTATUS,"")) > 0 Then
If Me!cboSTATUS <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me!cboSTATUS & "'"
End If
End If

strSQL = "SELECT * FROM MyTable " & IIf(Len(strSQL) > 0, " WHERE ", "")
& strSQL
Me.RecordSource = strSQL
End Sub

Then, in the AfterUpdate of each combo, add the following call:
BuildSQL

That's it. :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Gnerks said:
I have a form that has six combo boxes used for filtering information on
the
form. They are (in order)
cboScope (Filter options include =ALL, AF, DRU, FOA, MAJCOM) ;
cboMAJCOM (Filter options include = ALL, AF, AETC, ACC, AFMC, …);
cboMRS (filter options include = ALL, 1 MRS, 2 MRS, 3 MRS, …);
cboSTUDY TYPE (filter options include = ALL, CMS, MAS, S/S, FACTORS, …);
cboORG (filter options include: ALL, 2T2, AFSPC, ASU, …);
cboSTATUS (filter options include: ALL, COMPLETE, COMPLETE – IN STAFFING,
PUBLISHED…)
I need to be able to allow the user to filter these combo boxes one at a
time, all at once, or by any combination. For example the user may decide
to
filter the cboSCOPE to ‘AF’ and the filter cboMRS to ‘1 MRS’ .
I am not sure how to set my strWhere to allow for the filtering of
multiple
combo boxes. I have reviewed Allen Browne’s Client Search information,
however, I remain confused.
Code looks like:
If Me.cboMAJCOM > "ALL" Then
strWhere = strWhere & "([Scope] <> 'ALL') AND "
ElseIf Me.cboMAJCOM = "ALL" Then
strWhere = strWhere & "([Scope] = 'ALL') "
End If

'Remove the ending AND
'See if the string has more than 5 characters (a trailing " AND ") to
remove

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'nothing in the string
MsgBox "No Criteria", vbInformation, "Nothing to do"
Else
strWhere = Left$(strWhere, lngLen)

'apply the string as the form's filter
Me.Filter = strWhere
Me.FilterOn = True
End If

Can you help?
Click to expand...
 
G

Gnerks

Works like a charm - thank you! Will meet my deadline!

Graham R Seach said:
The usual way of doing this is to create a procedure for building a dynamic
SQL statement on-the-run.

Private Sub BuildSQL()
Dim strSQL As String

If Len(Nz(Me!cboScope,"")) > 0 Then
If Me!cboScope <> "ALL" Then
strSQL = "somefield = '" & me!cboScope & "'"
End If
End If

If Len(Nz(Me!MAJCOM,"")) > 0 Then
If Me!MAJCOM <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me!MAJCOM & "'"
End If
End If

If Len(Nz(Me!cboMRS,"")) > 0 Then
If Me!cboMRS <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me!cboMRS & "'"
End If
End If

If Len(Nz(Me![cboSTUDY TYPE],"")) > 0 Then
If Me![cboSTUDY TYPE] <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me![cboSTUDY TYPE] & "'"
End If
End If

If Len(Nz(Me!cboORG,"")) > 0 Then
If Me!cboORG <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me!cboORG & "'"
End If
End If

If Len(Nz(Me!cboSTATUS,"")) > 0 Then
If Me!cboSTATUS <> "ALL" Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "somefield = '" & me!cboSTATUS & "'"
End If
End If

strSQL = "SELECT * FROM MyTable " & IIf(Len(strSQL) > 0, " WHERE ", "")
& strSQL
Me.RecordSource = strSQL
End Sub

Then, in the AfterUpdate of each combo, add the following call:
BuildSQL

That's it. :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Gnerks said:
I have a form that has six combo boxes used for filtering information on
the
form. They are (in order)
cboScope (Filter options include =ALL, AF, DRU, FOA, MAJCOM) ;
cboMAJCOM (Filter options include = ALL, AF, AETC, ACC, AFMC, …);
cboMRS (filter options include = ALL, 1 MRS, 2 MRS, 3 MRS, …);
cboSTUDY TYPE (filter options include = ALL, CMS, MAS, S/S, FACTORS, …);
cboORG (filter options include: ALL, 2T2, AFSPC, ASU, …);
cboSTATUS (filter options include: ALL, COMPLETE, COMPLETE – IN STAFFING,
PUBLISHED…)
I need to be able to allow the user to filter these combo boxes one at a
time, all at once, or by any combination. For example the user may decide
to
filter the cboSCOPE to ‘AF’ and the filter cboMRS to ‘1 MRS’ .
I am not sure how to set my strWhere to allow for the filtering of
multiple
combo boxes. I have reviewed Allen Browne’s Client Search information,
however, I remain confused.
Code looks like:
If Me.cboMAJCOM > "ALL" Then
strWhere = strWhere & "([Scope] <> 'ALL') AND "
ElseIf Me.cboMAJCOM = "ALL" Then
strWhere = strWhere & "([Scope] = 'ALL') "
End If

'Remove the ending AND
'See if the string has more than 5 characters (a trailing " AND ") to
remove

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'nothing in the string
MsgBox "No Criteria", vbInformation, "Nothing to do"
Else
strWhere = Left$(strWhere, lngLen)

'apply the string as the form's filter
Me.Filter = strWhere
Me.FilterOn = True
End If

Can you help?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Overlapping Dates and Filtering Records 1
Report based on multiple combo boxes 2
Filter form using multiple combo boxes selection 6
Filter/Dependent Combo box problem... 0
Resetting Filter (Combo Box) to All 3
Filter Combo Box on multiple other combo boxes 1
Using Form Filter to Limit Unbound/Bound Combo Boxes Search Record 3
Combo Boxes as data source for a grapgh 2

Top