By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

filters

P: n/a
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

Dec 4 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.OpenReport. 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):
"DaysToSearch 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
Dec 4 '06 #2

P: n/a
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.faClass FROM aFaculty WHERE (((aFaculty.faClass) Is Not
Null)) GROUP BY aFaculty.faClass ORDER BY aFaculty.faClass 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.txtFilterFTPT
Case "{All}": wkFilter = ""
Case "FT": wkFilter = "([faStatus] = ""FT"") AND "
Case "PT": wkFilter = "([faStatus] <""FT"") AND "
End Select

If (Me.txtFilterTerm <"{All}") Then wkFilter = wkFilter &
"([faTerm] = """ & Me.txtFilterTerm & """) AND "
If (Me.txtFilterStatus <"{All}") Then wkFilter = wkFilter &
"([faStatus] = """ & Me.txtFilterStatus & """) AND "
If (Me.txtFilterClass <"{All}") Then wkFilter = wkFilter &
"([faClass] = """ & Me.txtFilterClass & """) AND "
If (Me.txtFilterDivision <"{All}") Then wkFilter = wkFilter &
"([faDivision] = """ & Me.txtFilterDivision & """) AND "
If (Me.txtFilterDept <"{All}") Then wkFilter = wkFilter &
"([faDept] = """ & Me.txtFilterDept & """) AND "
If (Len(Me.txtSearchFor) 0) Then
If ((InStr(Me.txtSearchFor, "*") 0) Or (InStr(Me.txtSearchFor,
"?") 0)) Then
wkFilter = wkFilter & "([" & Me.txtSearch & "] LIKE """ &
Me.txtSearchFor & """) AND "
Else
wkFilter = wkFilter & "([" & Me.txtSearch & "] = """ &
Me.txtSearchFor & """) AND "
End If
End If

If (Me.chkViewSelected = 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.Prog 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.txtFilterFTPT.Requery
Me.txtFilterStatus.Requery
Me.txtFilterClass.Requery
Me.txtFilterDivision.Requery
Me.txtFilterDept.Requery
Me.txtFilterTerm.Requery
Me.chkViewSelected = False

Me.txtFilterFTPT = "{All}"
Me.txtFilterStatus = "{All}"
Me.txtFilterClass = "{All}"
Me.txtFilterDivision = "{All}"
Me.txtFilterDept = "{All}"
Me.txtFilterTerm = "{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

Dec 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.