using a form to filter a report

[Afton]

I would like to make a form that filters a report by Supervisor and by
starting and ending date.
I have the supervisors in a combo box, and that works.
However, I do not know how to code to let the user enter a starting
and ending date and only show the records between those dates.
The code that I have so far does not work for the dates, but I'm
including it anyway so you show me what to change.
Thanks so much! :)

Here is my code (the supervisor combo box works, the start and ending
date text boxes do not):

Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strSupervisor As String
Dim strFilter As String
Dim strStart As String
Dim strEnd As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Salary data &
SS#-Current") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Supervisor field
If IsNull(Me.cboSupervisor.Value) Then
strSupervisor = "Like '*'"
Else
strSupervisor = "='" & Me.cboSupervisor.Value & "'"
End If
' Build criteria string for Starting date field
If IsNull(Me.Start.Value) Then
strStart = "Like '*'"
Else
strStart = "='" & Me.Start.Value & "'"
End If
' Build criteria string for Ending date field'
If IsNull(Me.End.Value) Then
strEnd = "Like '*'"
Else
strEnd = "='" & Me.End.Value & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[supervisor_f] " & strSupervisor & " AND [start_date]
" & strStart & " AND [end_date] " & strEnd
' strFilter = "[supervisor_f] " & strSupervisor
' Apply the filter and switch it on
With Reports![Salary data & SS#-Current]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![Salary data & SS#-Current].FilterOn = False
End Sub

[Bob Quintal]

You are using a string delimiter instead of a date delimiter in
your expressions.

strStart = "=#" & Me.Start.Value & "#"
strEnd = "=#" & Me.End.Value & "#"

The Hash (#) is for dates, the tick (') is for strings.
aj*******@hotmail.com (Afton) wrote in
news:76**************************@posting.google.c om:

I would like to make a form that filters a report by
Supervisor and by starting and ending date.
I have the supervisors in a combo box, and that works.
However, I do not know how to code to let the user enter a
starting and ending date and only show the records between
those dates. The code that I have so far does not work for the
dates, but I'm including it anyway so you show me what to
change. Thanks so much! :)

Here is my code (the supervisor combo box works, the start and
ending date text boxes do not):

Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strSupervisor As String
Dim strFilter As String
Dim strStart As String
Dim strEnd As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Salary data &
SS#-Current") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Supervisor field
If IsNull(Me.cboSupervisor.Value) Then
strSupervisor = "Like '*'"
Else
strSupervisor = "='" & Me.cboSupervisor.Value & "'"
End If
' Build criteria string for Starting date field
If IsNull(Me.Start.Value) Then
strStart = "Like '*'"
Else
strStart = "='" & Me.Start.Value & "'"
End If
' Build criteria string for Ending date field'
If IsNull(Me.End.Value) Then
strEnd = "Like '*'"
Else
strEnd = "='" & Me.End.Value & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[supervisor_f] " & strSupervisor & " AND
[start_date]
" & strStart & " AND [end_date] " & strEnd
' strFilter = "[supervisor_f] " & strSupervisor
' Apply the filter and switch it on
With Reports![Salary data & SS#-Current]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![Salary data & SS#-Current].FilterOn = False
End Sub

--
Bob Quintal

PA is y I've altered my email address.

[Tanya]

I haven't gone through all of your code. Have you considered using a
query as the record source of your report. Then, in the criteria of
the fields you want to prompt, reference the fields on the form. It
works really well, I use it all the time.

Let me know if you want to learn more.

aj*******@hotmail.com (Afton) wrote in message news:<76**************************@posting.google. com>...

I would like to make a form that filters a report by Supervisor and by
starting and ending date.
I have the supervisors in a combo box, and that works.
However, I do not know how to code to let the user enter a starting
and ending date and only show the records between those dates.
The code that I have so far does not work for the dates, but I'm
including it anyway so you show me what to change.
Thanks so much! :)

Here is my code (the supervisor combo box works, the start and ending
date text boxes do not):

Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strSupervisor As String
Dim strFilter As String
Dim strStart As String
Dim strEnd As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Salary data &
SS#-Current") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Supervisor field
If IsNull(Me.cboSupervisor.Value) Then
strSupervisor = "Like '*'"
Else
strSupervisor = "='" & Me.cboSupervisor.Value & "'"
End If
' Build criteria string for Starting date field
If IsNull(Me.Start.Value) Then
strStart = "Like '*'"
Else
strStart = "='" & Me.Start.Value & "'"
End If
' Build criteria string for Ending date field'
If IsNull(Me.End.Value) Then
strEnd = "Like '*'"
Else
strEnd = "='" & Me.End.Value & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[supervisor_f] " & strSupervisor & " AND [start_date]
" & strStart & " AND [end_date] " & strEnd
' strFilter = "[supervisor_f] " & strSupervisor
' Apply the filter and switch it on
With Reports![Salary data & SS#-Current]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![Salary data & SS#-Current].FilterOn = False
End Sub

[Pieter Linden]

aj*******@hotmail.com (Afton) wrote in message news:<76**************************@posting.google. com>...

I would like to make a form that filters a report by Supervisor and by
starting and ending date.
I have the supervisors in a combo box, and that works.
However, I do not know how to code to let the user enter a starting
and ending date and only show the records between those dates.
The code that I have so far does not work for the dates, but I'm
including it anyway so you show me what to change.
Thanks so much! :)

Build a valid WHERE clause (without the keyword WHERE) and then just
pass that in the filter of the report...

dim strFilter as string
Const cQUOTE As String = "'"

strFilter="[Supervisor]=" & cQUOTE & Me.cboSupervisor & cQUOTE
strFilter = strFilter & " AND [SomeDate] BETWEEN #" & Me.txtStartDate
& "# AND #" & Me.txtEndDate & "#"

Public Sub OpenAndPrintReport(ByVal strReport As String, ByVal
strFilter As String)
DoCmd.OpenReport strReport, acViewPreview, , strFilter,
acWindowNormal
End Sub