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 & ";"
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
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