?
Solved

Multifilter for Report

Posted on 2008-10-27
4
Medium Priority
?
283 Views
Last Modified: 2013-11-28
Hi Experts,

Thank you for your time and patience.

I am setting up a form with cascading combos to act as a filter for queries / reports.
On there form there are a number of cascading combos and four command buttons. Each command button opens a query and a report.
I have set up a similar form in my database where I set the criteria in the query to equal the combo box value using OR LIKE ... IS NULL statements to allow null selections; however the form I am working on at the moment has more combos and I am getting an error message "The query is too complex"

I have come accross this piece of code on EE and was wondering if I could use it. The code as is filters a subform.

Could I set the "strSWC" in the form code, then set the "strSQL" in the on click event of each command button?

How would I modify the code to suite?

Any other suggestions would be much appreciated.

Please note these are not my form or control names this is an example posted on EE.

Sub Mutifilter()
Dim strSQL as String
Dim strSWC as string   'SQL where clause

If not isnull(Me.cboPN) Then
   strSWC =  strSWC & "PartnerName='" & Me.cboPM & "' AND "
End if

If Not isnull(Me.cboM) Then
   strSWC = strSWC & "Material='" & Me.cboM & "' AND "
End IF

' similar for spec no, approved by
...

'Strip off the final AND Clause
If Not strSWC = vbNulLString Then
  strSWC = Left(strSWC,Len(strSWC) - 5)
End IF

'Compose the SQL for the subforms's recordsource
strSQL = "SELECT * FROM <your subforms recordsource>"
If Not strSWC = vbNullstring Then
  strSQL = strSQL & " WHERE " & strSWC
End IF

'Now update the recordsource for subform
'NB where frmSubForm is the name oof the subform object on the main form
Me.frmSubFrom.Form.Recordsource = strSQL

End Sub
0
Comment
Question by:dommac82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22824916
dommac82,

Systems like this can be tricky and complex.

Most developers would move up to actually building the SqL in the code.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22824940
0
 

Accepted Solution

by:
dommac82 earned 0 total points
ID: 22826375
Boag2000,

Thanks for the link. I think I found something in a post related to the link.
In one of the combos there are 1500 possibile selections; hence the cascading combos.

So I have simply created a string Where clause like in the example I gave, and added it to the Docmd.OpenReport statement. It works, but I am not confident. Do you see any potential problems doing it this way?

   Dim strWhere As String
    Dim rptLbyS As String
   

strWhere = "[Removed]=False AND [Fruit ID]='Blue'"
If Not IsNull(Me.cboType) Then
    strWhere = strWhere & "AND [Type ID]='" & Me.cboType & "'"
End If
If Not IsNull(Me.cboVariety) Then
    strWhere = strWhere & "AND [Variety]='" & Me.cboVariety & "'"
End If
If Not IsNull(Me.cboStage) Then
    strWhere = strWhere & "AND [Stage Name]='" & Me.cboStage & "'"
End If
If Not IsNull(Me.cboBlock) Then
    strWhere = strWhere & "AND [Block ID]='" & Me.cboBlock & "'"
End If
If Not IsNull(Me.cboSection) Then
    strWhere = strWhere & "AND [Section ID]='" & Me.cboSection & "'"
End If
If Not IsNull(Me.txtStartDate) Or Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & "AND [Planting Date] Between NZ('" & Me.txtStartDate & "',[Planting Date])"
    strWhere = strWhere & "AND NZ('" & Me.txtEndDate & "',[Planting Date])"
End If

'Set Report Name
rptLbyS = "rptBlueberryLocationBySection"

'Open Report
DoCmd.OpenReport rptLbyS, acPreview, , strWhere
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22827772
dommac82,

"Do you see any potential problems doing it this way"
Not really, ...hey as long as it gives you what you want...
;-)

JeffCoachman
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question