473,289 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Having difficulty creating reports with query parameters set from form controls

I have been having endless difficulty creating reports/queries that
set any relevent parameters from controls in forms.

I am creating an application under access 2003 but will target access
2000. The access file is in access 2000 format.

I have a form that will hold the relevent parameters for the
query/report that reports the statistics for all job records that
match a certain criteria. These are:
- A Customer Name.
- An Internal department.
- Start date (job must have been created after this date).
- End date (job must have been created before this date).

I have created the form that has two drop down lists, one containing
all customers in the database and the other containing all department
names. There are also two unbound text boxes on the field that hold
the start and end date for the query/report.

I have created a test query that lists all records that will match the
revelvent parmaters.

The base query displays:
- Job Number.
- Creation Date.
- AccountID (matches Customer Name).
- Identifier Department.

Everything works with the following setup (the query returns records)
with the critera field on the form set to:

AccountID : [Forms]![frmEnterCustAct]![AccountID]
Identifier Department : [Forms]![frmEnterCustAct]![IdDepart]

(frmEnterCustAct is the form that holds the input parameters).

Getting the date field right is more problematic. I have discovered
that the following will NOT work in the critera field for Creation
date in the query

Creation Date: >[Forms]![frmEnterCustAct]![TxtStartDate]

BUT THE FOLLOWING DOES WORK

Creation Date: >[Forms]![frmEnterCustAct]![TxtStartDate].[text]

Does anybody know why the criteria field must explicitly be set to the
text of the control?

When I set the criteria field for the Creation Date to field to what
should be its proper value:

Creation Date: >[Forms]![frmEnterCustAct]![TxtStartDate].[text] And
<[Forms]![frmEnterCustAct]![TxtEndDate].[Text]

This doesn't work. The query returns no records.

I have tried a number of ways around this problem involving using VBA
to set the query parameters from the On Open event in the report that
will open the query. I don't want to use DAO for this, only ADO. There
appears to be total lack of documentation that explains the interface
between the access queries and underlying ADO data object model that
allows the user to set the Query parameters of a report. Does anyone
know a reference to this. All the group threads I have read talk
abount DAO QueryDefs as the way to go.

I am at my wits end.

Thanks In advance.
Nov 13 '05 #1
1 2875
lo*****@iinet.net.au wrote:
I have been having endless difficulty creating reports/queries that
set any relevent parameters from controls in forms.

I am creating an application under access 2003 but will target access
2000. The access file is in access 2000 format.

I have a form that will hold the relevent parameters for the
query/report that reports the statistics for all job records that
match a certain criteria. These are:
- A Customer Name.
- An Internal department.
- Start date (job must have been created after this date).
- End date (job must have been created before this date).

I have created the form that has two drop down lists, one containing
all customers in the database and the other containing all department
names. There are also two unbound text boxes on the field that hold
the start and end date for the query/report.

I have created a test query that lists all records that will match the
revelvent parmaters.

The base query displays:
- Job Number.
- Creation Date.
- AccountID (matches Customer Name).
- Identifier Department.

Everything works with the following setup (the query returns records)
with the critera field on the form set to:

AccountID : [Forms]![frmEnterCustAct]![AccountID]
Identifier Department : [Forms]![frmEnterCustAct]![IdDepart]

(frmEnterCustAct is the form that holds the input parameters).

Getting the date field right is more problematic. I have discovered
that the following will NOT work in the critera field for Creation
date in the query

Creation Date: >[Forms]![frmEnterCustAct]![TxtStartDate]

BUT THE FOLLOWING DOES WORK

Creation Date: >[Forms]![frmEnterCustAct]![TxtStartDate].[text]

Does anybody know why the criteria field must explicitly be set to the
text of the control?

When I set the criteria field for the Creation Date to field to what
should be its proper value:

Creation Date: >[Forms]![frmEnterCustAct]![TxtStartDate].[text] And
<[Forms]![frmEnterCustAct]![TxtEndDate].[Text]

This doesn't work. The query returns no records.

I have tried a number of ways around this problem involving using VBA
to set the query parameters from the On Open event in the report that
will open the query. I don't want to use DAO for this, only ADO. There
appears to be total lack of documentation that explains the interface
between the access queries and underlying ADO data object model that
allows the user to set the Query parameters of a report. Does anyone
know a reference to this. All the group threads I have read talk
abount DAO QueryDefs as the way to go.

I am at my wits end.

Thanks In advance.


Dates are the date + 0 hour, 0 minutes, 0 seconds. If you store a value
like Now() to the date field ex: (9/6/04 13:01:02) and you want to get
records between 9/5/04 and 9/6/04, this record would not be in the list
since 13:01:02 is greater than 00:00:00. So IF you are using date/time
stamps, you'd want to select records between 9/5/04 and 9/7/04 since
that would now fall between 9/5/04 00:00:00 and 9/7/04 00:00:00

But to address your problem. Try something like this in your form for 1
of several alternatives. I also use >= since you might be wanting
records that are greater or equal to the date. Also, be aware that if
you are using dates, but the values are a date/time stamp, you need to
adjust the time.
Private Function PrintReport()
'remember, dates surrounded by #'s,
'alphanumerics by quotes, numbers aren't surrounded.
Dim strWhere As String
If Not IsNull(Me.TxtStartDate) Then
strWhere = "[Creation Date] >= #" & _
Me.TxtStartDate & "#"
ENdif
If Not IsNull(Me.TxtEndDate) Then
If strWhere > "" Then strWhere = strWHere & " And "
strWhere = strWhere & "[Creation Date] <= #" & _
Me.TxtEndDate & "#"
ENdif
'pass the date filters when command button to present report
'is pressed.
Docmd.OpenReport "YourReportName",,,strWhere
End Sub
Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Joris Kempen | last post by:
Hi people, I know that the question has come around sometimes: How to open an Access Report using ASP and export it to for example RTF. I'm trying to implement the first method of David...
2
by: Cassie Pennington | last post by:
I am using VBA to determine which query sits behind a report, depending upon the preferred sort order. This has worked perfectly for ordinary select queries behind reports, but it seems to be...
1
by: Fred Zuckerman | last post by:
I have 8 reports that I want to run. Each report is only about 6-7 lines long. I'd like to get them all on a single page. So I created a parent report and made each of the 8 desired reports a...
3
by: bcaponet | last post by:
I have a form where a user enters a date that I will then base a query on. In the past, I have simply placed Forms!! into the criteria for a query and it runs as long as the form is open. The...
4
by: Andy Davis | last post by:
I have developed a number of reports that are based on parameter queries where the user enters criteria such as a date range and a sales rep say. I want to be able to show a graphical picture in...
0
by: Henry | last post by:
Using ideas provided by some of you I was able to figure out how to get the names of the parameters fields of a crystal report specified at run time. The code below just basically puts the...
7
by: Jlo | last post by:
Hi, I have a c# winforms application. When I call the report file, it shows me all the records in the table. How can I make it to call only a particular range. i have the following code...
0
by: Albert D. Kallal | last post by:
Use the "where" clause, and make the reports sql *without* any parameters..and you not need to change the params.. So, you can build a un-bound form (a un-bound form is a form that is NOT...
7
by: google | last post by:
I am trying to automate the way reports are printed in an Access 2003 database - I have ~200 records (people) who require between 5 and 10 customized reports (depending on values within certain...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.