Query Parameter Syntax

[croy]

When I enter,

"Between [Forms]![frmStartExpansion]![txtStart] And
[Forms]![frmStartExpansion]![txtEnd]"

on the criteria line in a certain query (qryA), it runs
fine.

But later on, when I base a crosstab query on qryA, I get an
error message stating that the jet engine doesn't recognize,
'[Forms]![frmStartExpansion]![txtStart]' as a valid field
name or expression.

The built-in Help points me toward specifically stating the
parameter and its data-type.

But when I put,
"Between [Forms]![frmStartExpansion]![txtStart] And
[Forms]![frmStartExpansion]![txtEnd]"
as a parameter (in the query parameter dialog, and claim it
to be a Date & Time field, and then try to run the query, an
error comes up stating, "Invalid bracketing of name 'Between
[Forms]![frmStartExpansion]![txtStart] And
[Forms]![frmStartExpansion]![txtEnd]'."

I've tried all the variations that I can think of.

Is there a way to do this that will work?

 

[vanderghast]

Crosstabs require that the parameters type is declared, for each parameter.
With Access 2003, in the query designer, the menu, under Query, have the
item Parameters.... which allows you to declare each parameter, ex:
[Forms]![frmStartExpansion]![txtStart], and its expected type, a DateTime
.... maybe.



Vanderghast, Access MVP

 

[Lynn Trapp]

Don't enter the full string of the criteria, instead you need to create 2
parameters of Type Date/Time (I'm assuming that you are querying for start
and end dates). They should be:

[Forms]![frmStartExpansion]![txtStart]

and

[Forms]![frmStartExpansion]![txtEnd]

 

[John W. Vinson]

When I enter,

"Between [Forms]![frmStartExpansion]![txtStart] And
[Forms]![frmStartExpansion]![txtEnd]"

on the criteria line in a certain query (qryA), it runs
fine.

But later on, when I base a crosstab query on qryA, I get an
error message stating that the jet engine doesn't recognize,
'[Forms]![frmStartExpansion]![txtStart]' as a valid field
name or expression.

The built-in Help points me toward specifically stating the
parameter and its data-type.

But when I put,
"Between [Forms]![frmStartExpansion]![txtStart] And
[Forms]![frmStartExpansion]![txtEnd]"
as a parameter (in the query parameter dialog, and claim it
to be a Date & Time field, and then try to run the query, an
error comes up stating, "Invalid bracketing of name 'Between
[Forms]![frmStartExpansion]![txtStart] And
[Forms]![frmStartExpansion]![txtEnd]'."

I've tried all the variations that I can think of.

Is there a way to do this that will work?

It's always permissible (and very often a good idea) to explicitly declare a
query's parameters. For some reason, it's OBLIGATORY to do so for Crosstab
queries!

Open the query in SQL view and insert before the SELECT statement at the
beginning:

PARAMETERS [Forms]![frmStartExpansion]![txtStart] DateTime,
[Forms]![frmStartExpansion]![txtEnd] DateTime;
SELECT ....

Equivalently, open the query in the design grid; right click the grey
background of the tables and select Parameters. Copy and paste the form
references (they must match exactly!) into the left column and select
date/time (or the appropriate datatype for other parameters) in the right
column.

 

[croy]

Crosstabs require that the parameters type is declared, for each parameter.
With Access 2003, in the query designer, the menu, under Query, have the
item Parameters.... which allows you to declare each parameter, ex:
[Forms]![frmStartExpansion]![txtStart], and its expected type, a DateTime
... maybe.

Thanks Vanderghast. You and Lynn have saved the day here!

 

[croy]

Don't enter the full string of the criteria, instead you need to create 2
parameters of Type Date/Time (I'm assuming that you are querying for start
and end dates). They should be:

[Forms]![frmStartExpansion]![txtStart]

and

[Forms]![frmStartExpansion]![txtEnd]

Perfect! You and Vanderghast have saved the day here!

 

[croy]

It's always permissible (and very often a good idea) to explicitly declare a
query's parameters. For some reason, it's OBLIGATORY to do so for Crosstab
queries!

Open the query in SQL view and insert before the SELECT statement at the
beginning:

PARAMETERS [Forms]![frmStartExpansion]![txtStart] DateTime,
[Forms]![frmStartExpansion]![txtEnd] DateTime;
SELECT ....

Equivalently, open the query in the design grid; right click the grey
background of the tables and select Parameters. Copy and paste the form
references (they must match exactly!) into the left column and select
date/time (or the appropriate datatype for other parameters) in the right
column.

Excellent! Thanks John.