How Can I get Stored Query to Read Parameter from Open Form

[Gcook888]

SELECT tblDetail.MachineName, tblDetail.Operator, Max(tblDetail.Date) AS
MaxOfDate, Sum(tblDetail.TDHrs) AS SumOfTDHrs
FROM tblDetail

WHERE (((tblDetail.Date) Between [Forms]![frmReportDialog]![Start] And
[Forms]![frmReportDialog]![End]))

GROUP BY tblDetail.MachineName, tblDetail.Operator;

Its a Stored Query called qryX. When I run it, it returns no records. I know
the WHERE clause is the problem from trying to use it with a CHART wizard I got
runtime error when I tried to preview the chart..
The form frmReportDialog is open w/dates in its [Start] and [End] ctrls.

please help.

 

[Gcook888]

I know this is a classical example a lot of help is written on. I've read irt
all.

My problem in the query is:

Between [Forms]![frmReportDialog]![Start] And [Forms]![frmReportDialog]![End]

When the frmReportDialog is open I get no records when I know I should be. But
I don't get prompted.

If its not open I get prompted for [Forms]![frmReportDialog]![Start]
then
[Forms]![frmReportDialog]![End]

So it knows the form is there and open but its not interpreting the dates i am
putting in the ctrls [Start] and [End]

I am at my wits end

 

[Van T. Dinh]

1. Try

Between
Format(Forms!frmReportDialog!Start, "\#mm/dd/yyyy\#")
And
Format(Forms!frmReportDialog!End, "#\#mm/dd/yyyy\#")


2. Alternatively, you can declare the types for the
Parameters

PARAMETERS Forms!frmReportDialog!Start DateTime,
Forms!frmReportDialog!End Date Time;
SELECT ...

3. I *think* you can simply set a DateTime format on the
unbound Controls [Start] and [End] and Access seem to
recognise the entries as datetimes and pass them onto the
JET database as datetimes as well.

HTH
Van T. Dinh
MVP (Access)

 

[Gcook888]

3. I *think* you can simply set a DateTime format on the

unbound Controls [Start] and [End] and Access seem to
recognise the entries as datetimes and pass them onto the
JET database as datetimes as well.

HTH
Van T. Dinh
MVP (Access)

THANKS Van T. Dinh. The format property on the ctrls [start] and [End] is
already Short Date.

However 1. and 2. give me some new life. I haven't learned how to declare
Parameters like in 2. yet.

GC

 

[Gcook888]

2. Alternatively, you can declare the types for the

Parameters

PARAMETERS Forms!frmReportDialog!Start DateTime,
Forms!frmReportDialog!End Date Time;
SELECT ...

My chartwizard error pointed me towards this because it said:
Possible causes with Microsoft® Access:

You have a parameter in a crosstab query or in a query that a crosstab query or
chart is based on, and the parameter data type is not explicitly specified in
the Query Parameters dialog box.

To solve the problem:
In the query that contains the parameter, specify the parameter and its data
type in the Query Parameters dialog box. And;
Set the ColumnHeadings property for the query that contains the parameter.


But I had stopped doing the chartwixard I was only trying to get the query to
pick up the dates.

The last thing I had done was put [forms]![frmReportDialog]![start] and
[forms]![frmReportDialog]![end] in the Query Parameter Box with date types and
hadn't even noticed my SQL changed to make the Parameter declaration.

The last thing I did yesterday I tested this and it Still Didn't Work. Today I
turned Access on WithoutChangingAnything and the query worked.