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(acSysCmd GetObjectState, 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.cboSu pervisor.Value) Then
strSupervisor = "Like '*'"
Else
strSupervisor = "='" & Me.cboSuperviso r.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.V alue) 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 3 2895
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*******@hotma il.com (Afton) wrote in
news:76******** *************** ***@posting.goo gle.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(acSysCmd GetObjectState, 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.cboSu pervisor.Value) Then strSupervisor = "Like '*'" Else strSupervisor = "='" & Me.cboSuperviso r.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.V alue) 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.
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*******@hotma il.com (Afton) wrote in message news:<76******* *************** ****@posting.go ogle.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(acSysCmd GetObjectState, 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.cboSu pervisor.Value) Then strSupervisor = "Like '*'" Else strSupervisor = "='" & Me.cboSuperviso r.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.V alue) 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 aj*******@hotma il.com (Afton) wrote in message news:<76******* *************** ****@posting.go ogle.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.cboSuperviso r & cQUOTE
strFilter = strFilter & " AND [SomeDate] BETWEEN #" & Me.txtStartDate
& "# AND #" & Me.txtEndDate & "#"
Public Sub OpenAndPrintRep ort(ByVal strReport As String, ByVal
strFilter As String)
DoCmd.OpenRepor t strReport, acViewPreview, , strFilter,
acWindowNormal
End Sub This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: CSDunn |
last post by:
Hello,
I have a problem with field filtering between an Access 2000 Project form
(the application is called CELDT), and the report that shows the results of
the filter. Both the form and the report are based on the same View
addressed in the Record Source of both as 'dbo.CLMain_vw'. The View resides
in a SQL Server 2000 database. There are...
|
by: Simon Matthews |
last post by:
Hope someone can help an Access beginner!
I've just started keeping my surgical logbook on access and it's a
simple flat-file affair. I have created several queries that will
list cases performed at different hospitals and reports based on the
queries to print out the relavent details.
What I would like to do is have a summary sheet in...
|
by: Chasing Gates |
last post by:
I have created a database that brings in a new table weekly. I then
made a separate query for each sales rep and a separate report for each
rep. (The reports are all identical but call different queries.)
Lastly, I created a macro that sends the reports to the respective rep
via e-mail with the push of a button.
I am pleased with myself...
|
by: Grasshopper |
last post by:
Hi,
I am automating Access reports to PDF using PDF Writer 6.0. I've
created a DTS package to run the reports and schedule a job to run this
DTS package. If I PC Anywhere into the server on where the job is
running, the job runs sucessfully, PDF files got generated, everything
is good. If I scheduled the job to run at the time that I am...
|
by: |
last post by:
hi,
i have a form on which a user can choose specific criteria such as dates
etc, in order to filter the report that is called from the form.
i do this by using the Where section of the docmd.openreport as follows
DoCmd.OpenReport stDocName, acPreview, , strWhere
where strWhere is a string dependant on the choices the user makes in the
| |
by: KashMarsh |
last post by:
Access 2003
I need to have a user filter records on a linked, continuous form and
then I want to run various reports/queries from this recordset the user
created. I only need to see the PK values they filtered for.
On the form, there could be 10 controls displaying that the user
filters/sorts in whatever way they want to, but I only need...
|
by: emgallagher |
last post by:
I have a form which lists studies. People can filter the form based
on
details about the study, such as the study type. Currently users
filter via
the right click method.
I would like to be able to have a report that shows just the filtered
records.
The code that I tried which didn't work:
|
by: Cara Murphy |
last post by:
Hi There!
Hoping you are able to help me with a filtering scenario:
I have created a pop up form (Filter) to filter a report (Open Issues), which currently has a number of combo boxes which apply the filters to the fields in the report.
I would like to use this form to filter the report within a date range (Raised Date From and Raised...
|
by: phill86 |
last post by:
Hi,
I am filtering a report using the form filter with the following code
DoCmd.OpenReport "report1", acViewReport, , Me.filter
Which works fine untill I filter the form on one of the fields which is a lookup field
and I get the enter parameter value box which is looking for the Assetdetails.details field which contains the following...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |