Query with combination of multiple parameters

On my MS Access  Data Entry form I have two optional group controls.
First is opt_Category with next values:
1 – All
2 – Active
3 - Not Active
4 – In Process
5 – Completed

Second optional group control is opt_City
1- Paris
2- London
3 -Toronto
4- Amsterdam

On Main form I have sub forms (tabs) for each of those City where I enter some values and some dates
Aplication submited Date:
Aplication Process start Date:
Aplication Decision Date:
Notified Date:
In lower part of form I have detail sub form where I display results of proper selection combination.
Let say I want to show all applicants regardless of  Category or City or dates  entered in.
 I will show  applicants from Category and all of Cities regardless if date entry exist or not exist (as business rule allows to enter – register an applicant without any of dates present in).
If I want to show applicants from Paris and London that have submitted application (Submitted Date Is Not Null) or (Submitted Date is Null but Process Start Date Is Not Null) Business rule allow this kind of entry.
And so on…
What I want to say if I am creating separate query for each of those combination it will take a lot time and every new City added will multiply number of queries .
For now I have four Cities and number of Combination is 32 If I add next city it will go to 64.
How to create this selection criteria on fly? I assume It has to be “where” clause?
And I assume I need to loop through those two option group button to pull out which one is selected and put it in criteria – where clause - of select statement?
Any suggestion and example how to do this?
TarasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You only need a single query with criteria that comes from the main form.  With two criteria it is simple.  With 6 criteria, it's a little more complex.  The sample query below has a pattern that I hope you will understand so you can modify it if you want to add or delete criteria in the future.  The assumption is that every criterion is optional but when present, all criteria are connected with the AND operator.  Put two unbound combos and four pairs of dates or one pair that you will use for all four dates.

Select ...
From YourTable
Where
(Category = Forms!yourform!Opt_Category OR  Forms!yourform!Opt_Category Is Null)
AND (City = Forms!yourform!Opt_City OR Forms!yourform!Opt_City Is Null)
AND (SubDate Between Forms!yourform!FromSubDate AND Forms!yourform!ThruSubDate OR Forms!yourform!FromSubDate Is Null)
AND (ProcDate Between Forms!yourform!FromProcbDate AND Forms!yourform!ThruProcDate OR Forms!yourform!FromProcDate Is Null)
AND (DecDate Between Forms!yourform!FromDecDate AND Forms!yourform!ThruDecDate OR Forms!yourform!FromDecDate Is Null)
AND (NoteDate Between Forms!yourform!FromNoteDate AND Forms!yourform!ThruNoteDate OR Forms!yourform!FromNoteDate Is Null);

Although I always use querydefs, I would suggest using the QBE to build the select and From clauses and then switch to SQL View and entery the criteria formatted as above so it will be easy to read.  Copy the whole query or at least the where clause and paste it into your code as a comment.  Save the query while still in SQL view.  Open the form, choose some options and test the query.   Once the query works, you can add the code in a form button to run it.  The reason I'm suggesting that you save in SQL view and also copy the string and save it in a place where you can easily recover it is because if you switch the query to QBE view and save it, Access will rewrite the WHERE clause and it will be indecipherable due to the number of clauses and the combination of AND and OR operands.  Access does this to facilitate displaying complex criteria in the QBE.

So if you look at the clauses in the WHERE you will see the pattern.  (somecolumnname = formvariable OR formvariable is Null)  Each clause is connected to the others with an AND so that if two options are selected, both will be used .  However, the downside of making the criteria optional is that  if you select "Not Active" AND "Paris" what you actually end up with is "Not Active" OR "Paris".  If this will be a problem, then you will need to create the WHERE clause using VBA.  I can provide a sample if you need one.
0
TarasAuthor Commented:
Hi Pat that with Not Active OR Paris is problem and I have to handle it if you have  VBA code example it would help a lot.
0
PatHartmanCommented:
OK.  Then you'll need to build the SQL String on the fly.  Here is code from one of my more complex search forms.  This code just builds the SQL string.  Then a form is opened that gets the string I put in the Query field on the search form.  Basically, having the query string visible was to help me with debugging but I left it in the final version because it helps the users also.   If you need help with using the query once it is built, let us know.

Private Sub cmdViewResults_Click()
   On Error GoTo Err_Proc
    Call BuildSQL
    DoCmd.OpenForm "frmSearchResults", , , , , , Me.name
    Me.Visible = False

Exit_Proc:
   On Error GoTo 0
   Exit Sub

Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdViewResults_Click of VBA Document Form_frmClientSearch"
    End Select
End Sub

Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String

    strSelect = "Select * from qClientList "
    strSelectForExport = "Select * from qClientListExport "
    strOrderBy = " ORDER BY FullName"
    strWHERE = ""
    
    If Me.txtFirstName & "" = "" Then
    Else
        strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
    End If
    If Me.txtLastName & "" = "" Then
    Else
        strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtcity & "" = "" Then
    Else
        strCondition = "City Like " & QUOTE & Me.txtcity & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtaddress & "" = "" Then
    Else
        strCondition = "Address Like " & QUOTE & Me.txtaddress & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkBUP = True Then
        strCondition = "BUP = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.cboCareMgrID & "" = "" Then
    Else
        strCondition = "CareMgrID = " & Me.cboCareMgrID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.cboGenderID & "" = "" Then
    Else
        strCondition = "GenderID = " & Me.cboGenderID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtZip & "" = "" Then
    Else
        strCondition = "Zip = '" & Me.txtZip & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    If Me.cboRaceID & "" = "" Then
    Else
        strCondition = "RaceID = " & Me.cboRaceID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.cboDiagnosis & "" = "" Then
    Else
        strCondition = "Diagnosis = '" & Me.cboDiagnosis & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtpid & "" <> "" Then
        strCondition = "PID = '" & Me.txtpid & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtClientID & "" <> "" Then
        strCondition = "ClientID = " & Me.txtClientID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtems & "" <> "" Then
        strCondition = "EMS = '" & Me.txtems & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
        
    If Me.txtPhone & "" <> "" Then
        strCondition = "(Phone = '" & Me.txtPhone & "' OR CellPhone = '" & Me.txtPhone & "')"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    If Me.cboCounty & "" <> "" Then
        strCondition = "County = '" & Me.cboCounty & "'"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
        
    If Me.cboLevelID & "" <> "" Then
        strCondition = "LevelID = " & Me.cboLevelID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    If Me.cboTier & "" <> "" Then
        strCondition = "PCATier = " & Me.cboTier
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    If Me.cboProgramID & "" = "" Then
    Else
        strCondition = "ProgramID = " & Me.cboProgramID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    'Funding
    If Me.chkT19 = True Then
        strCondition = "T19 = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    If Me.chkSelf = True Then
        strCondition = "Self = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkMFP = True Then
        strCondition = "MFP = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    If Me.chkNOTT19 = True Then
        strCondition = "Not(T19 = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkNOTSelf = True Then
        strCondition = "Not (Self = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkNOTMFP = True Then
        strCondition = "Not(MFP = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    Select Case Me.fraStatus
        Case 1
            strCondition = "StatusID = 1318"          'Open
        Case 2
            strCondition = "StatusID = 1319"          'Closed
        Case 4
            strCondition = "StatusID = 1427"          'Pending
        Case Else
            strCondition = ""
    End Select
    If strWHERE = "" Then
        If strCondition = "" Then
        Else
            strWHERE = strCondition
        End If
    Else
        If strCondition = "" Then
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    
'query for form/report
    If strWHERE = "" Then
        strSQL = strSelect
    Else
        strSQL = strSelect & " WHERE " & strWHERE
    End If

    strSQL = strSQL & strOrderBy
    Me.txtQuery = strSQL
    
'query for Excel
    If strWHERE = "" Then
        strSQL = strSelectForExport
    Else
        strSQL = strSelectForExport & " WHERE " & strWHERE
    End If
    
    strSQL = strSQL & strOrderBy
    Me.txtQueryExport = strSQL
End Sub

Open in new window


And a picture of the form:search.PNG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
To help me understand the problem, assume:
First is opt_Category with next values:
 1 – All
 2 – Active

 Second optional group control is opt_City
 1- Paris
 2- London

Please show the required output.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.