Pass a Value to a CrossTab Query

C

Claudette Hennessy

I am attempting to pass a parameter to a Crosstab query from a dialog form.
A report is created from the query, and embedded in another report as a
subform. i.e,

QuestionnaireReport

Various info.........
(Following is the report whose source is the X-tab query)

Event

Physicals Totals M
F
10 5
5


Choices for event selected from the dialog form combo box are "Physicals",
"Eye Tests", "Hearing Test", .......

The SQL for the X-Tab is

TRANSFORM Count(qryQuestionnaireResults.EventDate) AS CountOfEventDate
SELECT qryQuestionnaireResults.EventName, qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventDate) AS [Event Date Total]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.Gender;

If I substitute "Physicals" for [Forms]![frmEventDialog]![cboEvent], it
works fine. otherwise I get a "Microsoft Jet Engine does not recognize
Forms]![frmEventDialog]![cboEvent].

Can't I do this, or am I doing it wrong? How can I pass a value to the
CrossTab query at report time?
 
D

Duane Hookom

You must enter the data types of your parameters. Select Query->Parameters
and enter
[Forms]![frmEventDialog]![cboEvent] Text
 
C

Claudette Hennessy

Thank you for your quick response, but after entering your suggestion in the
Query Parameter when attempting to execute the query, I get an error
message, Invalid bracketing of name '[[Forms]![frmEventDialog]![cboEvent]]'
I looked at the SQL, which was

PARAMETERS [[Forms]![frmEventDialog]![cboEvent] ] Text ( 255 );
TRANSFORM Count(qryQuestionnaireResults.EventType) AS CountOfEventType
SELECT qryQuestionnaireResults.EventName, qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventType) AS [Total Of EventType]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.TownOfResidence; I took the extra brackets
off of the SQL, and the query runs fine. ????

However the results do not show up in the main report because the system is
needs fixed column headings?

Duane Hookom said:
You must enter the data types of your parameters. Select Query->Parameters
and enter
[Forms]![frmEventDialog]![cboEvent] Text

--
Duane Hookom
MS Access MVP
--

Claudette Hennessy said:
I am attempting to pass a parameter to a Crosstab query from a dialog
form. A report is created from the query, and embedded in another report
as a subform. i.e,

QuestionnaireReport

Various info.........
(Following is the report whose source is the X-tab query)

Event

Physicals Totals M F
10
5 5


Choices for event selected from the dialog form combo box are
"Physicals", "Eye Tests", "Hearing Test", .......

The SQL for the X-Tab is

TRANSFORM Count(qryQuestionnaireResults.EventDate) AS CountOfEventDate
SELECT qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventDate) AS [Event Date Total]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.Gender;

If I substitute "Physicals" for [Forms]![frmEventDialog]![cboEvent], it
works fine. otherwise I get a "Microsoft Jet Engine does not recognize
Forms]![frmEventDialog]![cboEvent].

Can't I do this, or am I doing it wrong? How can I pass a value to the
CrossTab query at report time?
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Claudette,

PMFBI

the error message is correct, try changing
PARAMETERS clause to (in SQL View):

PARAMETERS Forms!frmEventDialog!cboEvent Text ( 255 );

or

PARAMETERS [Forms]![frmEventDialog]![cboEvent] Text ( 255 );

Apologies again for butting in,

gary

Claudette Hennessy said:
Thank you for your quick response, but after entering your suggestion in
the Query Parameter when attempting to execute the query, I get an error
message, Invalid bracketing of name
'[[Forms]![frmEventDialog]![cboEvent]]' I looked at the SQL, which was

PARAMETERS [[Forms]![frmEventDialog]![cboEvent] ] Text ( 255 );
TRANSFORM Count(qryQuestionnaireResults.EventType) AS CountOfEventType
SELECT qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventType) AS [Total Of EventType]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.TownOfResidence; I took the extra brackets
off of the SQL, and the query runs fine. ????

However the results do not show up in the main report because the system
is needs fixed column headings?

Duane Hookom said:
You must enter the data types of your parameters. Select
Query->Parameters and enter
[Forms]![frmEventDialog]![cboEvent] Text

--
Duane Hookom
MS Access MVP
--

Claudette Hennessy said:
I am attempting to pass a parameter to a Crosstab query from a dialog
form. A report is created from the query, and embedded in another report
as a subform. i.e,

QuestionnaireReport

Various info.........
(Following is the report whose source is the X-tab query)

Event

Physicals Totals M F
10 5 5


Choices for event selected from the dialog form combo box are
"Physicals", "Eye Tests", "Hearing Test", .......

The SQL for the X-Tab is

TRANSFORM Count(qryQuestionnaireResults.EventDate) AS CountOfEventDate
SELECT qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventDate) AS [Event Date Total]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.Gender;

If I substitute "Physicals" for [Forms]![frmEventDialog]![cboEvent], it
works fine. otherwise I get a "Microsoft Jet Engine does not recognize
Forms]![frmEventDialog]![cboEvent].

Can't I do this, or am I doing it wrong? How can I pass a value to the
CrossTab query at report time?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Access has a glitch in it that sometimes adds an extra set of [] around the
parameter you are declaring.

Remove the outside set of [] and your query should work.

PARAMETERS [Forms]![frmEventDialog]![cboEvent] Text ( 255 );
TRANSFORM ...

Claudette said:
Thank you for your quick response, but after entering your suggestion in the
Query Parameter when attempting to execute the query, I get an error
message, Invalid bracketing of name '[[Forms]![frmEventDialog]![cboEvent]]'
I looked at the SQL, which was

PARAMETERS [[Forms]![frmEventDialog]![cboEvent] ] Text ( 255 );
TRANSFORM Count(qryQuestionnaireResults.EventType) AS CountOfEventType
SELECT qryQuestionnaireResults.EventName, qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventType) AS [Total Of EventType]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.TownOfResidence; I took the extra brackets
off of the SQL, and the query runs fine. ????

However the results do not show up in the main report because the system is
needs fixed column headings?

Duane Hookom said:
You must enter the data types of your parameters. Select Query->Parameters
and enter
[Forms]![frmEventDialog]![cboEvent] Text

--
Duane Hookom
MS Access MVP
--

Claudette Hennessy said:
I am attempting to pass a parameter to a Crosstab query from a dialog
form. A report is created from the query, and embedded in another report
as a subform. i.e,

QuestionnaireReport

Various info.........
(Following is the report whose source is the X-tab query)

Event

Physicals Totals M F
10
5 5


Choices for event selected from the dialog form combo box are
"Physicals", "Eye Tests", "Hearing Test", .......

The SQL for the X-Tab is

TRANSFORM Count(qryQuestionnaireResults.EventDate) AS CountOfEventDate
SELECT qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventDate) AS [Event Date Total]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.Gender;

If I substitute "Physicals" for [Forms]![frmEventDialog]![cboEvent], it
works fine. otherwise I get a "Microsoft Jet Engine does not recognize
Forms]![frmEventDialog]![cboEvent].

Can't I do this, or am I doing it wrong? How can I pass a value to the
CrossTab query at report time?
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

No apologies required Gary. Thanks for jumping in.

--
Duane Hookom
MS Access MVP
--

Gary Walter said:
Hi Claudette,

PMFBI

the error message is correct, try changing
PARAMETERS clause to (in SQL View):

PARAMETERS Forms!frmEventDialog!cboEvent Text ( 255 );

or

PARAMETERS [Forms]![frmEventDialog]![cboEvent] Text ( 255 );

Apologies again for butting in,

gary

Claudette Hennessy said:
Thank you for your quick response, but after entering your suggestion in
the Query Parameter when attempting to execute the query, I get an error
message, Invalid bracketing of name
'[[Forms]![frmEventDialog]![cboEvent]]' I looked at the SQL, which was

PARAMETERS [[Forms]![frmEventDialog]![cboEvent] ] Text ( 255 );
TRANSFORM Count(qryQuestionnaireResults.EventType) AS CountOfEventType
SELECT qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventType) AS [Total Of EventType]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.TownOfResidence; I took the extra brackets
off of the SQL, and the query runs fine. ????

However the results do not show up in the main report because the system
is needs fixed column headings?

Duane Hookom said:
You must enter the data types of your parameters. Select
Query->Parameters and enter
[Forms]![frmEventDialog]![cboEvent] Text

--
Duane Hookom
MS Access MVP
--

I am attempting to pass a parameter to a Crosstab query from a dialog
form. A report is created from the query, and embedded in another report
as a subform. i.e,

QuestionnaireReport

Various info.........
(Following is the report whose source is the X-tab query)

Event

Physicals Totals M F
10 5 5


Choices for event selected from the dialog form combo box are
"Physicals", "Eye Tests", "Hearing Test", .......

The SQL for the X-Tab is

TRANSFORM Count(qryQuestionnaireResults.EventDate) AS CountOfEventDate
SELECT qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventDate) AS [Event Date Total]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.Gender;

If I substitute "Physicals" for [Forms]![frmEventDialog]![cboEvent], it
works fine. otherwise I get a "Microsoft Jet Engine does not recognize
Forms]![frmEventDialog]![cboEvent].

Can't I do this, or am I doing it wrong? How can I pass a value to the
CrossTab query at report time?
Click to expand...
Click to expand...
Click to expand...
 
C

Claudette Hennessy

Dear All,

Followed everyone's advice, and after declaring the column headings in the
X-Tab query, the report works. Thanks to everybody. This news group is a
wonderful resource.

Is it documented somewhere under what circumstances the Parameter must be
entered in the Query Parameter Dialog Box? (Besides X-Tabs) Ordinary
[Enter City Desired] and BETWEEN [Enter Date1] AND [Enter Date2] parameters
run fine. To be honest, it never occured to me that the error message from
the Jet engine meant I needed to use the PARAMETER statement.

John Spencer said:
Access has a glitch in it that sometimes adds an extra set of [] around
the
parameter you are declaring.

Remove the outside set of [] and your query should work.

PARAMETERS [Forms]![frmEventDialog]![cboEvent] Text ( 255 );
TRANSFORM ...

Claudette said:
Thank you for your quick response, but after entering your suggestion in
the
Query Parameter when attempting to execute the query, I get an error
message, Invalid bracketing of name
'[[Forms]![frmEventDialog]![cboEvent]]'
I looked at the SQL, which was

PARAMETERS [[Forms]![frmEventDialog]![cboEvent] ] Text ( 255 );
TRANSFORM Count(qryQuestionnaireResults.EventType) AS CountOfEventType
SELECT qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventType) AS [Total Of EventType]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.TownOfResidence; I took the extra brackets
off of the SQL, and the query runs fine. ????

However the results do not show up in the main report because the system
is
needs fixed column headings?

Duane Hookom said:
You must enter the data types of your parameters. Select
Query->Parameters
and enter
[Forms]![frmEventDialog]![cboEvent] Text

--
Duane Hookom
MS Access MVP
--

I am attempting to pass a parameter to a Crosstab query from a dialog
form. A report is created from the query, and embedded in another
report
as a subform. i.e,

QuestionnaireReport

Various info.........
(Following is the report whose source is the X-tab query)

Event

Physicals Totals M F
10
5 5


Choices for event selected from the dialog form combo box are
"Physicals", "Eye Tests", "Hearing Test", .......

The SQL for the X-Tab is

TRANSFORM Count(qryQuestionnaireResults.EventDate) AS CountOfEventDate
SELECT qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate,
Count(qryQuestionnaireResults.EventDate) AS [Event Date Total]
FROM qryQuestionnaireResults
WHERE
(((qryQuestionnaireResults.EventName)=[Forms]![frmEventDialog]![cboEvent]))
GROUP BY qryQuestionnaireResults.EventName,
qryQuestionnaireResults.EventDate
PIVOT qryQuestionnaireResults.Gender;

If I substitute "Physicals" for [Forms]![frmEventDialog]![cboEvent],
it
works fine. otherwise I get a "Microsoft Jet Engine does not recognize
Forms]![frmEventDialog]![cboEvent].

Can't I do this, or am I doing it wrong? How can I pass a value to
the
CrossTab query at report time?
Click to expand...
Click to expand...
Click to expand...
 

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

dcount in a crosstab query 3
crosstab query 2
Crosstab query error 1
string crosstab query 1
How to Union Crosstabs... 1
How to convert Null values to zero when create an average query 1
Crosstab queries as subforms error: "You can't use a pass-through. 3
Crosstab query to include fields with Null value 2

Top