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.