Best Way to Create Custom Filtering on a Report

[alan_mitchell]

Hi all,

Here's my situation.

I have a report (Report_1) based on a query (Query_1) which contains the
following fields:


Staff name (text)
Manager name (text)
Location (text)
Client (text)
Key client (yes/no)
Date payment due (date)
Overdue (yes/no)


I want to create some sort of filtering interface at the top of the report,
which lets users filter records from a particular location, staff member,
overdue status etc.

The user should be able to filter only the fields he wishes, such as showing
all clients from UK, or all clients belonging to Joe Bloggs that are overdue.

Any ideas on the best way to approach this?

Cheers,
Alan

 

[Duane Hookom]

I would create an unbound form with text, combo, list, and check boxes that
allow the user to set any filter they desire. Then use code to build a WHERE
CONDITION to use in the DoCmd.OpenReport method.

 

[zohaibnazish]

Dear Duane,

I am having same kind of issues as well. Now I have an unbound form
with some combo and text boxes which I am using to generate reports of
desired fields/values. What I want is a kind of a check box or button
next to the field so that when selected, the reports gets grouped to
that field. Is it possible? If so then what kind of VBA/SQL/Expression
will be required as I am new to this. Many thanks.

Regards

Zebe

 

[Duane Hookom]

If you want to be able to sort/group a report by different fields/expressions
at run-time, check the code posted on Allen Browne's page
http://www.allenbrowne.com/ser-33.html.

 

[alan_mitchell]

I've not used the DoCmd.OpenReport method with WHERE CONDITION before.

For my example above, say I wanted to open the report where the user has
selected the following conditions:

[Form_1].[Staff_Name] = Joe Bloggs
[Form_1].[Overdue] = yes

(all other fields left blank)

How would I write the expression?

DoCmd.OpenReport "Report_1", acViewPreview, , WHERE...

Are there any good articles / tutorials on this you recommend?

Cheers,
Alan

 

[Duane Hookom]

I don't know why you would ever hard-code Joe Bloggs into an expression.

Assuming you have controls on your form named txtStaff_Name (text box) and
chkOverdue (check box). Also, assuming Overdue is a yes/no field.

Dim strWhere as String
Dim strReport as String
strWhere = "1=1 "
strReport = "Report_1"
If Not IsNull(Me.txtStaff_Name) Then
strWhere = strWhere & " AND [Staff_Name]=""" & _
Me.txtStaff_Name & """ "
End If
If Not IsNull(Me.chkOverdue) Then
strWhere = strWhere & " AND [OverDue]= " & Me.chkOverdue
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere

 

[alan_mitchell]

Thanks Duane,

Your code worked great.

I started adding new text fields and it worked fine also.

However, when I started adding more check boxes, I started getting runtime
data mismatch errors (3464):


--------------------------------------------------------------------------

Private Sub Button_Run_Report_Click()


Dim strWhere As String
Dim strReport As String
strWhere = "1=1 "
strReport = "Rec_Report_2"



'SCM NAME
If Not IsNull(Me.txt_scm_name) Then
strWhere = strWhere & " AND [seniorname]=""" & _
Me.txt_scm_name & """ "
End If

'SECM NAME
If Not IsNull(Me.txt_secm_name) Then
strWhere = strWhere & " AND [searchexecusername]=""" & _
Me.txt_secm_name & """ "
End If

'CAMPAIGN NAME
If Not IsNull(Me.txt_campaign_name) Then
strWhere = strWhere & " AND [campaignname]=""" & _
Me.txt_campaign_name & """ "
End If


'TARGET DATE SET
If Not IsNull(Me.chk_target_date_set) Then
strWhere = strWhere & " AND [AgreedCompletionDateSet]=""" & _
Me.chk_target_date_set & """ "
End If

'NOT RELEVANT SAYS SCM
If Not IsNull(Me.chk_not_relevant_says_scm) Then
strWhere = strWhere & " AND [ActionNotRelevantSaysSCM]=""" & _
Me.chk_not_relevant_says_scm & """ "
End If


'NOT RELEVANT SAYS SECM
If Not IsNull(Me.chk_not_relevant_says_secm) Then
strWhere = strWhere & " AND [ActionNotRelevant]=""" & _
Me.chk_not_relevant_says_secm & """ "
End If

'COMPLETED
If Not IsNull(Me.chk_completed) Then
strWhere = strWhere & " AND [ActionCompleted]= " & Me.chk_completed
End If


DoCmd.OpenReport strReport, acViewPreview, , strWhere

--------------------------------------------------------------------------


Any idea what I've done wrong here?

Cheers,
Alan

 

[Duane Hookom]

The "data type" errors are caused by expressions comparing numeric values to
string values. String values must be delimited with quotes, dates with "#",
and numerics without delimiters.

 

[alan_mitchell]

Hi Duane,

Think I'm almost there, but I'm getting a error message which is really
bugging me. Text fields have quotes, numerics don't. Can't see where I've
gone wrong - any ideas?


----------------------------------------------------
Run-time error '3075'.

Syntax error (missing operator) in query expression '1=1 AND
[staffname]="a.brown" AND [campaignname]="campaign1" AND [keyclient]= AND
[overdue]= -1'

 

[Duane Hookom]

It would help to see your code that creates the where condition. It is clear
that you aren't pulling a keyclient filter. Either [keyclient] should be
excluded from the expression or there should be a -1 or 0 in this section:
AND [keyclient]= AND

 

[alan_mitchell]

Hi Duane,

Code below:

-----------------------------------------------------------------------
Private Sub Button_Run_Report_Click()

Dim strWhere As String
Dim strReport As String
strWhere = "1=1 "
strReport = "Rec_Report_2"

If Not IsNull(Me.staffname) Then
strWhere = strWhere & " AND [staffname]=""" & _
Me.staffname & """ "
End If

If Not IsNull(Me.campaignname) Then
strWhere = strWhere & " AND [campaignname]=""" & _
Me.campaignname & """ "
End If

If Not IsNull(Me.keyclient) Then
strWhere = strWhere & " AND [keyclient]= " & Me.keyclient
End If

If Not IsNull(Me.overdue) Then
strWhere = strWhere & " AND [overdue]= " & Me.overdue
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub

-----------------------------------------------------------------------

 

[Duane Hookom]

Apparently you are using a check box on the form for KeyClient. This works
well for reporting either checked or not checked. Would you want to print
both keyclient and not keyclient? If so, I would change the check box to an
option group with options for both, key, and not key. Otherwise try:

If Not IsNull(Me.keyclient) Then
strWhere = strWhere & " AND [keyclient]= " & Nz(Me.keyclient,0)
End If

 

[alan_mitchell]

Thanks Duane,

It's all working fine now. I think the problem was how it checks if the
value is null.

Using If Not IsNull(me.keyclient) somehow kept returning values even if the
value was null.

Instead I changed the condition to If Me.keyclient <> "", as per the thread
below, which did the job nicely.

http://www.webdeveloper.com/forum/showthread.php?t=141500

Thanks for all your help with this.

Alan