Filtering by several criteria

[BruceM]

I am doing some work in a Customer Calls database. Oversimplifying a bit,
calls are either complaints or compliments. I am using the click event of a
command button on an unbound form (frmParameter) to provide a date range
directly (rather than providing query criteria) using a slight adaptation of
code Allen Browne provided on his web site:

Dim strReport As String 'Name of report to be opened
Dim strField As String 'Name of date field
Dim strWhere As String 'Where condition for OpenReport
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptFeedback"
strField = "FeedbackDate"

If IsNull(Me.txtStart) And IsNull(Me.txtEnd) Then
DoCmd.OpenReport strReport, acViewPreview
Else
If Not IsNull(Me.txtEnd) And IsNull(Me.txtStart) Then 'End
date only; no Start
strWhere = strField & " <= " & Format(Me.txtEnd,
conDateFormat)
Else 'Start and End dates provided
strWhere = strField & " Between " & Format(Me.txtStart,
conDateFormat) & _
" And " & Format(Me.txtEnd, conDateFormat) And
Me.grpSelectAll = 3
End If
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere

I would also like to give the users a choice between Complaints only,
Compliments only, and All (both complaints and compliments). To that end I
have provided an option group on frmParameter, with the idea that I can
specify All, Complaint, or Compliment in addition to the date range (the
default is All). Complaint_Compliment is a text field. I would rather not
use an assortment of queries to accomplish this, so I guess I am trying to
figure out how to specify the parameters dynamically. Especially vexing has
been my attempt to specify a value for the Complaint_Compliment text field
in something like this:
strSQL = "SELECT * from tblCalls WHERE Complaint_Compliment = ???"

 

[Allen Browne]

Bruce, you are already using a form where the user enters the dates, so you
could add a combo to the form where they can choose between complaints and
compliments.

The basic idea is to end up with a string that looks like the WHERE clause
in a query. You can mock up a query that works using any literal values for
the critiera, and then switch it to SQL View (View menu, in query design) to
see the WHERE clause part of the SQL statement. It will look something like
this:
(FeedbackDate) = #1/1/2006#) AND (Complaint_Compliment = 'Complaint')

The actual implemention will depend on the field types. For an example of
how to build such a WhereCondition string from whichever boxes contain an
entry, download this sample:
http://allenbrowne.com/unlinked/Search2000.zip
It is for Access 2000 or later. Just a few kb. The sample actully
illustrates building the Filter for a form, but the WhereCondition is the
Filter for the report, so the code is identical.

 

[BruceM]

Allen,

Thanks for the reply. However, I cannot get it to work. One problem is
that there needs to be the option of viewing both complaints and
compliments. In other words, include all records no matter the value in
[Complaint_Compliment], which means I cannot use the value of
[Complaint_Compliment] as part of the filter. I have constructed the SQL as
you have suggested, but cannot translate it into a string that can be used
to filter the report recordset. I know that I need to add another half
dozen or so quote marks, but I can't sort out where. I have attempted to
use an option group (since radio buttons were requested, and since the most
common choice will be not to filter based on [Complaint_Compliment]). To
that end I tried to define strWhere based on the option selected:

If Me.grpSelectAll = 1 Then '1 is All Records, 2 is Complaints, 3 is
Compliments
strWhere = ?????

I have tried this as an experiment (choosing only Compliment records)

strComp = "Compliment"
strWhere = "([Complaint_Compliment] = """ & strComp & """) AND"

but ) is expected instead of the second &.

I know that I could build several queries and use grpSelectAll to determine
which one to use in the report's Open event, but I am greatly frustrated
that I cannot find a way to filter for Complaint_Compliment as I have for
date.

 

[BruceM]

I have just learned that strComp is a reserved word. I'll post again after
some experiments.

Allen,

Thanks for the reply. However, I cannot get it to work. One problem is
that there needs to be the option of viewing both complaints and
compliments. In other words, include all records no matter the value in
[Complaint_Compliment], which means I cannot use the value of
[Complaint_Compliment] as part of the filter. I have constructed the SQL
as you have suggested, but cannot translate it into a string that can be
used to filter the report recordset. I know that I need to add another
half dozen or so quote marks, but I can't sort out where. I have
attempted to use an option group (since radio buttons were requested, and
since the most common choice will be not to filter based on
[Complaint_Compliment]). To that end I tried to define strWhere based on
the option selected:

If Me.grpSelectAll = 1 Then '1 is All Records, 2 is Complaints, 3 is
Compliments
strWhere = ?????

I have tried this as an experiment (choosing only Compliment records)

strComp = "Compliment"
strWhere = "([Complaint_Compliment] = """ & strComp & """) AND"

but ) is expected instead of the second &.

I know that I could build several queries and use grpSelectAll to
determine which one to use in the report's Open event, but I am greatly
frustrated that I cannot find a way to filter for Complaint_Compliment as
I have for date.

Bruce, you are already using a form where the user enters the dates, so
you could add a combo to the form where they can choose between
complaints and compliments.

The basic idea is to end up with a string that looks like the WHERE
clause in a query. You can mock up a query that works using any literal
values for the critiera, and then switch it to SQL View (View menu, in
query design) to see the WHERE clause part of the SQL statement. It will
look something like this:
(FeedbackDate) = #1/1/2006#) AND (Complaint_Compliment = 'Complaint')

The actual implemention will depend on the field types. For an example of
how to build such a WhereCondition string from whichever boxes contain an
entry, download this sample:
http://allenbrowne.com/unlinked/Search2000.zip
It is for Access 2000 or later. Just a few kb. The sample actully
illustrates building the Filter for a form, but the WhereCondition is the
Filter for the report, so the code is identical.

 

[BruceM]

Allen,

Just wanted to let you know that I sorted it out with this code in the
unbound form that provides report parameters:

Dim strReport As String 'Name of report to be opened
Dim strField As String 'Name of date field
Dim strWhere As String 'Where condition for OpenReport
Dim strCategory As String
Dim strComplaint As String, strCompliment As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptFeedback"
strField = "FeedbackDate"

If Me.grpSelectAll = 2 Then
strCategory = "Complaint"
Else
If Me.grpSelectAll = 3 Then
strCategory = "Compliment"
Else
strCategory = "Complaint"" Or ""Compliment"
End If
End If

strWhere = "([Complaint_Compliment] = """ & strCategory & """) AND "

If IsNull(Me.txtStart) And IsNull(Me.txtEnd) Then
DoCmd.OpenReport strReport, acViewPreview
Else
If Not IsNull(Me.txtEnd) And IsNull(Me.txtStart) Then 'End date
only; no Start
strWhere = strWhere & strField & _
" <= " & Format(Me.txtEnd, conDateFormat)
Else 'Start and End dates provided
strWhere = strWhere & strField & _
" Between " & Format(Me.txtStart, conDateFormat) & _
" And " & Format(Me.txtEnd, conDateFormat)
End If
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere


I'm not sure I found the best way to pass along the grpSelectAll
information, but it seems to work.

By the way, this is how I ended up adding the date range to the report (in
the report header print event). TxtReportTitle is an unbound text box.

Dim strDateRange As String
Dim strStart As String
Dim strEnd As String
Dim strMin As String
Dim strMax As String

strMin = DMin("[FeedbackDate]", "tblCustFeedback")
strMax = DMax("[FeedbackDate]", "tblCustFeedback")
strStart = Nz(Forms!frmParam!txtStart, strMin)
strEnd = Nz(Forms!frmParam!txtEnd, strMax)

Me.txtReportTitle = "From " & Format(strStart, "dd-mmm-yyyy") & " to " &
Format(strEnd, "dd-mmm-yyyy")

Thanks for all of the code and other information you make available on your
web site. It is greatly appreciated.