filters

[brino]

hi all !

my client wants to bring out data into a report with a number of
filters that are chosen by the user .
eg. -Section
-Days to search
-Type of care

ive never tried this before so im not sure how to go about it.
the tricky part is that there are a number of options to choose in each
of these filters or the user can choose "ALL" in each of the the
filters which is the part im finding hard.
how do i do this in a query or number of querys ??

thanks

brino

[Tom van Stiphout]

On 4 Dec 2006 03:15:33 -0800, "brino" <bd************ *@yahoo.com.au>
wrote:

Filtering is applying a WHERE clause, which is one of the arguments of
DoCmd.OpenRepor t. Your job is to inspect the selections on the form,
and concatenate the right one.
You might pass in:
"Section=1 and DaysToSearch between #12/1/2006# and #12/31/2006#"
or if All sections have been chosen, that part of the WHERE clause
does not apply (does not need to restrict):
"DaysToSear ch between #12/1/2006# and #12/31/2006#"

-Tom.

>hi all !

my client wants to bring out data into a report with a number of
filters that are chosen by the user .
eg. -Section
-Days to search
-Type of care

ive never tried this before so im not sure how to go about it.
the tricky part is that there are a number of options to choose in each
of these filters or the user can choose "ALL" in each of the the
filters which is the part im finding hard.
how do i do this in a query or number of querys ??

thanks

brino

[King Ron]

As TVS said, the easiest way is to build up a filter by checking each
of the filter selectors and appending a 'where' condition to the
Form.Filter property. I generally use combo boxes to allow the user to
select a value from each of the fields (or a pair of combo boxes that
allows one to select from a 'Field List' on a query and then set the
value to seek}. I UNION my combobox row source with the literal "{All}"
- the curly brackets sort the 'all' value to the top of the combobox
list:

<sample combobox 'Table/Query' rowsource>

SELECT aFaculty.faClas s FROM aFaculty WHERE (((aFaculty.faC lass) Is Not
Null)) GROUP BY aFaculty.faClas s ORDER BY aFaculty.faClas s UNION SELECT
"{All}" FROM uSysCtl;

</sample combobox 'Table/Query' rowsource>

Note that uSysCtl is my generic system control values table. It can
have only one row, so the dummy 'SELECT' statement actually only
returns the literal '{All}'. This combobox looks at the recordset I'm
setting the filter on and returns a list of all distinct values of
aFaculty("Class ").

Them add an Event to each of the filter controls 'OnChange'. In this
sample application, all of the filter controls _OnChange events
reference a common subroutine SetFilter().

<Combobox OnChange Event>

Private Sub txtFilterClass_ Change()
SetFilter
End Sub

</Combobox OnChange Event>

and finally the SetFilter subroutine:

<SQL filtering example>
Private Sub SetFilter()
On Error GoTo SetFilterErr

Dim wkFilter As String

wkFilter = ""
Select Case Me.txtFilterFTP T
Case "{All}": wkFilter = ""
Case "FT": wkFilter = "([faStatus] = ""FT"") AND "
Case "PT": wkFilter = "([faStatus] <""FT"") AND "
End Select

If (Me.txtFilterTe rm <"{All}") Then wkFilter = wkFilter &
"([faTerm] = """ & Me.txtFilterTer m & """) AND "
If (Me.txtFilterSt atus <"{All}") Then wkFilter = wkFilter &
"([faStatus] = """ & Me.txtFilterSta tus & """) AND "
If (Me.txtFilterCl ass <"{All}") Then wkFilter = wkFilter &
"([faClass] = """ & Me.txtFilterCla ss & """) AND "
If (Me.txtFilterDi vision <"{All}") Then wkFilter = wkFilter &
"([faDivision] = """ & Me.txtFilterDiv ision & """) AND "
If (Me.txtFilterDe pt <"{All}") Then wkFilter = wkFilter &
"([faDept] = """ & Me.txtFilterDep t & """) AND "
If (Len(Me.txtSear chFor) 0) Then
If ((InStr(Me.txtS earchFor, "*") 0) Or (InStr(Me.txtSe archFor,
"?") 0)) Then
wkFilter = wkFilter & "([" & Me.txtSearch & "] LIKE """ &
Me.txtSearchFor & """) AND "
Else
wkFilter = wkFilter & "([" & Me.txtSearch & "] = """ &
Me.txtSearchFor & """) AND "
End If
End If

If (Me.chkViewSele cted = True) Then wkFilter = wkFilter & "([faOK] =
True) AND "

If (Len(wkFilter) = 0) Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = Left(wkFilter, Len(wkFilter) - 5)
Me.FilterOn = True
End If

Forms.frmMain.P rog 0, DCount("[faEmplID]", "aFaculty", "[faOK] =
True") & " faculty members selected."

SetFilterExit:
Exit Sub

SetFilterErr:
Select Case Err
Case 438:
Resume Next
Case Else
Resume SetFilterExit
End Select
End Sub
</SQL filtering example>

So this example appends a substring for any active filters together to
create the final SQL 'Where' clause that the .Filter property is set
to.

Finally, this routine updates each filtering combobox in case any new
values have been entered and clears any filters that have been set:

<Clear filtering comboboxes>

Private Sub ResetFilter()
Me.txtFilterFTP T.Requery
Me.txtFilterSta tus.Requery
Me.txtFilterCla ss.Requery
Me.txtFilterDiv ision.Requery
Me.txtFilterDep t.Requery
Me.txtFilterTer m.Requery
Me.chkViewSelec ted = False

Me.txtFilterFTP T = "{All}"
Me.txtFilterSta tus = "{All}"
Me.txtFilterCla ss = "{All}"
Me.txtFilterDiv ision = "{All}"
Me.txtFilterDep t = "{All}"
Me.txtFilterTer m = "{All}"

Me.txtSearch = ""
Me.txtSearchFor = ""
SetFilter
End Sub

</Clear filtering comboboxes>

Hopefully all this is clear. If anything is not let me know. Google
Groups autom,atically text wraps so some of the VBA will throw an error
if you just copy and paste. You'll have to getr rid of the extra line
feeds that are inserted.
Ron, King of Chi