Query Parameter Syntax


C

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?
 
Ad

Advertisements

V

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
 
L

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]
 
J

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.
 
C

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!
 
C

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!
 
Ad

Advertisements

C

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Parameter Syntax 2
Date Problem 11
List Box 4
Parameters for query from form 5
Running a Report From A Crosstab Query 1
UNION QUERY (Sorting) 2
Date Filter Problem 2
Looking for better performance 4

Top