Having difficulty creating reports with query parameters set from form controls

[longtim]

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.

[Salad]

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