Multiple filters on form

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.
 
G

Guest

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...
 
C

cathywoodford

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
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtering, RecordsetClone, Main Form - Subform Issues 4
Error 3464 Data type mismatch in criteria expression for between 2 dates look up 3
Filter by form with beginning date and enddate, where calender we 1
Tracking changes to a form 3
Multi-select List Box output to Excel File 29
Tracking changes to a form's fields---still searching for an answe 6
Syntax error 18
Dialog Form for Date Variables Won't Close 7

Top