Multiple filters on form

  • Thread starter Thread starter cathywoodford
  • Start date Start date
C

cathywoodford

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.
 
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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


[email protected] said:
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.
Click to expand...
 
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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



[email protected] said:
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.
Click to expand...
Private Sub cmdPrintReport_Click()
On Error GoTo Err_cmdPrintReport_Click
Click to expand...
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"
Click to expand...
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
Click to expand...
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
Click to expand...
If lstrSQL = "" Then
MsgBox "Please choose a company from the list"
Else
stDocName = "rptReportbyCompany"
DoCmd.OpenReport stDocName, acPreview, , lstrSQL
Click to expand...
End If
Click to expand...
Exit_cmdPrintReport_Click:
Exit Sub
Click to expand...
Err_cmdPrintReport_Click:
MsgBox Err.Description
Resume Exit_cmdPrintReport_Click
Click to expand...
End Sub
Click to expand...
Hope someone can help.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
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
--
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
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
Click to expand...
 
Back
Top