By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,239 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

using a form to filter a report

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.