Multiple filters on form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cathywoodford@personainternet.com - 10 May 2007 18:23 GMT

Hi. I'm creating a report based on a company combo box selection and
a date range (2 text box fields). I can't seem to get the report to
do both. I want it so that the combo box selection (company) must be
choosen but the date range is optional. If it's not filled in then it
would return all rows for that company. Here is my code.

Private Sub cmdPrintReport_Click()
On Error GoTo Err_cmdPrintReport_Click

Dim lstrSQL As String
Dim stDocName As String
Dim strDate As String
Dim strField As String 'Name of your date field.
Dim strwhere As String
strField = "tbltransmittal.DateofIssue"

If IsNull(Me.txtStartIssueDate) Then
If Not IsNull(Me.txtEndIssueDate) Then 'End date, but no
start.
strDate = strField & " <= " & Me.txtEndIssueDate
End If
Else
If IsNull(Me.txtEndIssueDate) Then 'Start date, but no
End.
strDate = strField & " >= " & (Me.txtStartIssueDate)
Else 'Both start and end dates.
strDate = strField & " Between " & (Me.txtStartIssueDate)
_
& " And " & (Me.txtEndIssueDate)
End If
End If

If Not IsNull(Me.cboCompanySearch) Or Me.cboCompanySearch <> ""
Then
If lstrSQL <> "" Then
lstrSQL = lstrSQL & " and "
End If
lstrSQL = lstrSQL & " [CompanyName] Like " & """" &
Nz(Me.cboCompanySearch, "*") & strDate & """"
End If

If lstrSQL = "" Then
MsgBox "Please choose a company from the list"
Else
stDocName = "rptReportbyCompany"
DoCmd.OpenReport stDocName, acPreview, , lstrSQL

End If

Exit_cmdPrintReport_Click:
Exit Sub

Err_cmdPrintReport_Click:
MsgBox Err.Description
Resume Exit_cmdPrintReport_Click

End Sub

Hope someone can help.

Reply to this Message

SteveS - 11 May 2007 07:33 GMT

Hi Cathy,

I re-wrote your code a little. Try the following code:

' The following two lines should be at the top of every code page
'--------------------------------------------------
Option Compare Database
Option Explicit

'---------------------------------------------------------
Private Sub cmdPrintReport_Click()
On Error GoTo Err_cmdPrintReport_Click

Dim lstrSQL As String
Dim stDocName As String
Dim strDate As String
Dim strField As String 'Name of your date field.

strField = "tbltransmittal.DateofIssue"

If IsNull(Me.txtStartIssueDate) And IsNull(Me.txtEndIssueDate) Then
'neither date entered
strDate = ""
ElseIf Not IsNull(Me.txtStartIssueDate) And IsNull(Me.txtEndIssueDate) Then
'Start date entered
strDate = strField & " >= #" & (Me.txtStartIssueDate) & "#"
ElseIf IsNull(Me.txtStartIssueDate) And Not IsNull(Me.txtEndIssueDate) Then
'end date entered
strDate = strField & " <= #" & Me.txtEndIssueDate & "#"
Else
'default both dates entered
strDate = strField & " Between #" & (Me.txtStartIssueDate) & "#"
strDate = strDate & " And #" & (Me.txtEndIssueDate) & "#"
End If

If IsNull(Me.cboCompanySearch) Or Me.cboCompanySearch = "" Then
MsgBox "Please choose a company from the list"
Else
lstrSQL = "[CompanyName] Like '" & Me.cboCompanySearch & "'"
If Len(strDate) > 0 Then
'add the date filter
lstrSQL = lstrSQL & " AND " & strDate
End If

'Debug.Print lstrSQL

' open report
stDocName = "rptReportbyCompany"
DoCmd.OpenReport stDocName, acPreview, , lstrSQL
End If

Exit_cmdPrintReport_Click:
Exit Sub

Err_cmdPrintReport_Click:
MsgBox Err.Description
Resume Exit_cmdPrintReport_Click

End Sub
'-----------------------------------------------------

HTH

Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

> Hi. I'm creating a report based on a company combo box selection and
> a date range (2 text box fields). I can't seem to get the report to
[quoted text clipped - 55 lines]
>
> Hope someone can help.

Reply to this Message

cathywoodford@personainternet.com - 11 May 2007 15:22 GMT

> Hi Cathy,
>
[quoted text clipped - 126 lines]
>
> - Show quoted text -

Thanks for your help Steve!! It worked like a charm. If I wanted to
add something else to filter by I would just add another if statement
in this section
If Not IsNull(Me.cboCompanySearch) Or Me.cboCompanySearch <> "" Then
If lstrSQL <> "" Then
lstrSQL = lstrSQL & " and "
End If
lstrSQL = lstrSQL & " [CompanyName] Like " & """" &
Nz(Me.cboCompanySearch, "*") & strDate & """"
End If

Cathy

Reply to this Message

SteveS - 12 May 2007 08:48 GMT

Hi Cathy,

Here is the code modified (again). This will make it easier to add more
criteria.

I added an example (see the code) of limiting the report to one state. If
the name of the field in the table is [txtState] and the control is a combo
box named cboSatate, the code would looklike this:

'---beg code ---------
Private Sub cmdPrintReport_Click()
On Error GoTo Err_cmdPrintReport_Click

Dim lstrSQL As String
Dim stDocName As String
Dim strDate As String
Dim strField As String 'Name of your date field.

strField = "tbltransmittal.DateofIssue"
stDocName = "rptReportbyCompany"

'Company selection required---------------
If IsNull(Me.cboCompanySearch) Or Me.cboCompanySearch = "" Then
MsgBox "Please choose a company from the list"
Me.cboCompanySearch.SetFocus
Me.cboCompanySearch.Dropdown
Exit Sub
Else
lstrSQL = "[CompanyName] Like '" & Me.cboCompanySearch & "'"
End If

'check for Date criteria-------------------------
If IsNull(Me.txtStartIssueDate) And IsNull(Me.txtEndIssueDate) Then
'neither date entered
strDate = ""
ElseIf Not IsNull(Me.txtStartIssueDate) And IsNull(Me.txtEndIssueDate) Then
'Start date entered
strDate = strField & " >= #" & (Me.txtStartIssueDate) & "#"
ElseIf IsNull(Me.txtStartIssueDate) And Not IsNull(Me.txtEndIssueDate) Then
'end date entered
strDate = strField & " <= #" & Me.txtEndIssueDate & "#"
Else
'default both dates entered
strDate = strField & " Between #" & (Me.txtStartIssueDate) & "#"
strDate = strDate & " And #" & (Me.txtEndIssueDate) & "#"
End If

'add date criteria, if any
If Len(strDate) > 0 Then
'add the date filter
lstrSQL = lstrSQL & " AND " & strDate
End If

'---example if new criteria--------

' add State criteria ----------------
' If Not IsNull(Me.cboState) Then
' lstrSQL = lstrSQL & " AND [txtState] = '" & Me.cboState & "'"
' End If
'-----------

'Debug.Print lstrSQL

' open report
DoCmd.OpenReport stDocName, acPreview, , lstrSQL

Exit_cmdPrintReport_Click:
Exit Sub

Err_cmdPrintReport_Click:
MsgBox Err.Description
Resume Exit_cmdPrintReport_Click

End Sub
'---end code ----------

Remember to use the proper delimiters:

numbers - no delimiters
text - delimiters are single or double quotes
dates - delimiters are the hash sign (#)

HTH

Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

> Thanks for your help Steve!! It worked like a charm. If I wanted to
> add something else to filter by I would just add another if statement
[quoted text clipped - 8 lines]
>
> Cathy

Reply to this Message