Alright, I give up! I'm asking the experts. I have created a database that calculates PTO for employees. I need to be able to cache the report by user entered dates and specific employee names. I have been able to get the dates to work with the following VBA script: - ..
-
Private Sub cmdOK_Click()
-
-
Dim strReport As String 'Name of report to open.
-
Dim strField As String 'Name of your date field.
-
Dim strWhere As String 'Where condition for OpenReport.
-
Const conDateFormat = "\#mm\/dd\/yyyy\#"
-
-
strReport = "PTO Form"
-
strField = "DateUsed"
-
-
If IsNull(Me.txtStartDate) Then
-
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
-
strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
-
End If
-
Else
-
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
-
strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
-
Else 'Both start and end dates.
-
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
-
& " And " & Format(Me.txtEndDate, conDateFormat)
-
End If
-
-
End If
-
-
-
-
' Debug.Print strWhere 'For debugging purposes only.
-
-
-
DoCmd.OpenReport strReport, acViewPreview, , strWhere
-
-
-
End Sub
However, the field that I have also put in, EmpName, which is a combo box, I cannot get it to filter by. Please help :)
9 2386 nico5038 3,080
Recognized Expert Specialist
A combobox can be referred to like other fields like:
Me.comboname
There's however a catch as the combo can hold multiple columns and only the "bound" coulmn (see the Data property tab) will be returned. When you need e.g. the second column use:
Me.comboname.co lumn(1)
The index is "zero based" and will start with 0 for the forst column, etc.
Nic;o)
NeoPa 32,579
Recognized Expert Moderator MVP
I looked through your code, which illustrates some good understanding of Date filtering (a concept most people struggle with badly). One minor point, the (\) is not necessary before the (/) for the format string (It's right for the (#)s though :) - Nice).
Anyway, Please check out this tutorial ( Example Filtering on a Form.) for help with your filtering. If you find you're still stuck after that then please come back and explain where your difficulty(ies) lie and we'll do what we can to help.
I had tried using the "filter a form" instructional, and I just can't seem to get it to take. I am struggling with where I should add the additional filter to the code and how exactly to add it. I've been able to do so much with access, that I know I must be stumbling on a mundane block! Please advise :)
Thanks
NeoPa 32,579
Recognized Expert Moderator MVP
Can you tell me which chapter you got up to and what your confusion is exactly?
You know there is an example database attached with the finished article in don't you?
I have read through all seven chapters about 5 times now and I'm getting stuck at the filter portion. I don't have a specific filter set for this form, not sure how to do it so that it would leave it open for change every time the form pops up...
The mail problem is that I am not sure where to put the extra filter into the code and how to do so...
NeoPa 32,579
Recognized Expert Moderator MVP
I'm afraid you're going to have to be a lot clearer than that. What you say makes very little sense on its own (If I had your database in front of me then it might, but I don't). You need to explain in clear simple English, what you need.
What does this even mean ? :confused:
The mail problem is that I am not sure where to put the extra filter into the code and how to do so...
Rabbit 12,516
Recognized Expert Moderator MVP
I think it's just a typo. Mail = Main. But other that that, I too am confused on exactly what they want.
All I want is to add another filter into the code that I posted above. The combo box that I have in the form is "Combo6" and it reflects the text box "EmpName" on the report that is generated from the aforementioned form. When trying to use the instructions, it is not bound to a specific table. It is just a form with a report tied to it that is limited by selections made on that form. There is only one list in the combo box, not several different to choose from, they are all in text form within the combo box. Hopefully this helps a little further...
NeoPa 32,579
Recognized Expert Moderator MVP
Assuming the field in the underlying record source of the report is also called [EmpName] you would need something like : - ..
-
Private Sub cmdOK_Click()
-
-
Dim strReport As String 'Name of report to open.
-
Dim strField As String 'Name of your date field.
-
Dim strWhere As String 'Where condition for OpenReport.
-
Const conDateFormat = "\#mm/dd/yyyy\#"
-
-
strReport = "PTO Form"
-
strField = "DateUsed"
-
-
If IsNull(Me.txtStartDate) Then
-
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
-
strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
-
End If
-
Else
-
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
-
strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
-
Else 'Both start and end dates.
-
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
-
& " And " & Format(Me.txtEndDate, conDateFormat)
-
End If
-
-
End If
-
-
If Not IsNull(Me!Combo6) Then
-
strWhere = "((" & strWhere & ") AND ([EmpName]='" & Me!Combo6 & "'))"
-
End If
-
-
-
-
' Debug.Print strWhere 'For debugging purposes only.
-
-
-
DoCmd.OpenReport strReport, acViewPreview, , strWhere
-
-
-
End Sub
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Felicity |
last post by:
Hello,
I have the following basic XML file:
<ProductX>
<Settings>
<FormA>
<TextBox1>
<value>ABC</value>
</TextBox1>
|
by: diskoduro |
last post by:
Hi!
I'm trying to get help to an unexpected problem that has appeared
while I was writing a new application.
I want to opeon a report of product sales by filtering previously from
a listbox in a previous form. In the listbox I have the complete
products list and I select the ones I want to be presented in the
report. I push a cmdButton and generate a String (wherecondition)
which I use with docmd.openreport. The string is generated this...
|
by: mooseshoes |
last post by:
All:
I'm using Access 2000 on a Windows XP platform.
My goal is to use a form to gather user criteria which I will then parse
into a useable SQL string. At this point I would like to open one or more
reports and use the query as the recordsource.
What would be a common approach to doing this? I have been using ADO
methods up until this point for other tasks.
|
by: Armando |
last post by:
Here's one that's going to leave me bald before long -
I have a report (no headers or footers, small detail section) that runs
normally when I open it from the database window. When I run it from code:
DoCmd.OpenReport "rpt_Name", acViewPreview
I get five records on the first page, and only one record on the second
|
by: Denise |
last post by:
How do I get the report to not spend time pulling up the data when I'm
going to bail out of the report anyway?
Below is the code in my report. I first show the user a form where
they can select some filtering parameters which I put in Getz's
TaggedValues class. But they can hit a Cancel button if they decide
they don't want any report. When I test the Cancel button I'd like the
report to close immediately but it seems to spend some...
| |
by: David Lozzi |
last post by:
Howdy,
I have a user control that is a report to display data. On the page the
control is inserted in, I have filter options to filter the report. When I
try to do something like this, nothing happens.
dim filt as string
... build filter string...
UserControl.ReportFilter = filt
|
by: Sheryl 37 |
last post by:
Can any1 give me the coding for the following problem.....How to i filter data in Crystal Report using a combo option (e.g Company Name).
There1 combo box for the Company, and a crystal report.
I'm using VB.Net 2005
Please help me i been figureing out for 2 weeks .....
|
by: AA Arens |
last post by:
I have a create report function showing reports for every record that
is listed in the tblCalls table.
In my situation one report per call that comes from a company.
I would like to have a company pre-selected before the forms are
created and it then shows only the reports from the selected company.
The data in the reports comes from the table Calls that has various
fields. These fields are filled with the frmCalls form
|
by: jim190 |
last post by:
I am relatively new to using VBA and am have been trying for two days to pass a user entered value to my report for filtering when opened. I have the following subroutine in the open event of my report:
Private Sub Report_Open(Cancel As Integer)
'Prompts for business unit when run educational assistance reports.
Dim strBusUnitName As String
strBusUnitName = UCase(InputBox("Enter Business Unit or click OK for all.", "Business Unit",...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |