Full Version: Multi-Criteria Search
UtterAccess Forums > Microsoft® Access > Access Forms
LAM
I am trying to create a multi-critieria search using Kforemans example found here:
http://www.utteraccess.com/forum/Complete-...-S-t776573.html
would like users to filter the agreements form by clicking on the appropriate button. There are times when they will want to see just open agreements or just closed agreements. That is easy enough, but users will also want to filter agreements according to completed date and/or closed date.
Following the example I created a query based on the table that is used as the control source for the form. What I understand from the code is that it will set the criteria for the query and then return that record set to the form. Is that correct?
There is a section of code that I do not understand and I am not sure how to edit it for use with my form.
The section of code that I am having trouble with:
CODE
If Len(strWhere) > 0 Then
    If Nz(DCount("*", "qrySearchCriteria", left(strWhere, Len(strWhere) - 5)), 0) > 0 Then
       strWhere = "WHERE " & left(strWhere, Len(strWhere) - 5)
       strFind = "SELECT Distinct [date], [lookupid], " & _
       strFind = "SELECT Distinct [pk], [date], [lookupid], " & _
            "[text] " & _
            "FROM qrySearchCriteria " & strWhere & ";"

I am not sure of what to put for strFind. Do I enter the fields that I am filtering?
The entire code I have entered so far:
CODE
Private Sub cmdApplyFilter_Click()
Dim strFind As String, strWhere As String
If Len(Me.txtStsTID & vbNullString) > 0 Then
        strWhere = strWhere & "AgreementLogTbl.al_stsTID=" & Me.txtStsTID & " AND "
    End If
    If Len(Me.txtFromDateCom & Me.TxtToDateCom & vbNullString) > 0 Then
        strWhere = strWhere & "ManageAgreementsFrm.al_datecom Between #" & txtFromDateCom & "# And #" & TxtToDateCom & "#" & " AND "
        If IsNull(Me.txtFromDateCom) Then
            MsgBox "You must input the beginning sent date."
            Me.txtFromDateCom.SetFocus
            Exit Sub
             End If
        If IsNull(Me.TxtToDateCom) Then
            MsgBox "You must input the ending sent date."
            Me.TxtToDateCom.SetFocus
            Exit Sub
        End If
    End If
    
If Len(Me.TxtFromCloseDate & Me.TxtToCloseDate & vbNullString) > 0 Then
        strWhere = strWhere & "ManageAgreementsFrm.al_closedate Between #" & TxtFromCloseDate & "# And #" & TxtToCloseDate & "#" & " AND "
        If IsNull(Me.TxtFromCloseDate) Then
            MsgBox "You must input the beginning close date."
            Me.TxtFromCloseDate.SetFocus
            Exit Sub
             End If
        If IsNull(Me.TxtToCloseDate) Then
            MsgBox "You must input the ending close date."
            Me.TxtToCloseDate.SetFocus
            Exit Sub
        End If
    End If
    
If Len(strWhere) > 0 Then
    If Nz(DCount("*", "qrySearchCriteria", left(strWhere, Len(strWhere) - 5)), 0) > 0 Then
       strWhere = "WHERE " & left(strWhere, Len(strWhere) - 5)
      strFind = "SELECT Distinct [ALID], [al_stsTID], [al_datecom], " & _
            "[al_closedate] " & _
            "FROM qrySearchCritieria " & strWhere & ";"
        
        
        Dim strDocName As String
        strDocName = "ManageAgreementsFrm"
        DoCmd.OpenForm strDocName
        Forms![ManageAgreementsFrm].Form.RecordSource = strFind
        Forms![ManageAgreementsFrm].Form.Requery
        Dim nTotalRecords As Integer
        nTotalRecords = DCount("*", "qrySearchCritieria", right(strWhere, Len(strWhere) - 6))
        If nTotalRecords = 1 Then
        MsgBox nTotalRecords & " record found."
        Else
        MsgBox nTotalRecords & " records found."
        End If
        Else
        MsgBox "No records matching your criteria were found."
        End If
    Else
    MsgBox "Please enter search criteria."
    End If
End Sub

I am not sure that this will even work for this form. In the code there is a message box to prompt users for a date if it is blank. I would actually like it to return all records if one of the date ranges is empty. For example the user may want to look at all closed agreements with a close date between 9/1/2010 and 9/30/2010, but they don't care when the agreement was opened.
Is there a better or easier way to accomplish my goal?
Thank you.
LAM
ArmenStein
This code looks a bit over-complicated to me, with the Dcounts, field validations and all. In the book "Access 2007 VBA Programmer's Reference" (Wrox), I demonstrated a technique to have selection criteria at the top of a continuous form. It would be great if you bought the book < but you don't need to. The sample code is the Chamber application in "Chapter 8, 9, 15" sample code at:
http://www.wrox.com/WileyCDA/WroxTitle/Acc...d-DOWNLOAD.html
Basically, you build a Where clause based on the fields, then you alter the form's recordsource property by swapping in your new Where clause.
The open-ended date ranges are very handy, and can be handled with If statements. For example, if the beginning date is Null, you just build the Where clause to use <= EndingDate.
Another example of flexible criteria by building Where clauses (for reports this time) is on our free J Street Downloads page at http://ow.ly/M58Y
See "Report Selection Techniques".
Hope this gets you started,
Armen
LAM
Armen,
Thank you for the information. I will take a look and try it out, but probably not until later this week.
LAM
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.