crosstab query report with parameter

[Souris]

I have a report using crosstab with parameter.

The reprot works without parameter.

The report does not give me fields list to choose when I use report wizard
with parameter.

The report continues to ask me to enter the parameter when I open the report
with parameter crosstab query?

Does report support parameter crosstab?
If yes, where I should look for the issue?

Thanks millions in advance,

 

[Duane Hookom]

Is your form open with a value entered into the control that is used to
filter the crosstab? Does your crosstab have values entered into the Column
Headings property?

 

[Souris]

Thanks for the message,
I just started to create report. I do not have form to enter value.
No I do not value enter in to the column heading property. I just created
paramter for the cross tab query.

Does Crosstab query have any limitations or must work for any specific way?

I have some issues like pass parameter and subquery with inner join in union
query.
The union query get result, but the crosstab query does not recognize the
fields in the join field. Crosstab query works when I take out the join in my
uinon query.

Are there any crosstab query information I need study?


Thanks again,

 

[Rick Brandt]

Thanks for the message,
I just started to create report. I do not have form to enter value.
No I do not value enter in to the column heading property. I just
created paramter for the cross tab query.

Does Crosstab query have any limitations or must work for any
specific way?

Queries have a parameter form you can open when in the designer that allows
you to specify the DataType of your parameters. While this can be done in
any query and is always a good idea it is *required* that you do so for
Crosstab queries. It is even required if you have a parameter being used in
a query which is then subsequently used as the input to a Crosstab query
that doesn't have any of its own parameters.

 

[Souris]

UNION

SELECT 19 as MyID, ActivityDate as [Activity Date], tblEmployee.first_name
+ " " + tblEmployee.Last_Name as EDA,
(Select count(*) from tblEDAActivityPlanMeetingDetails where
tblEDAActivityPlanMeetingDetails.MeetingElements = 5 and datediff("d",
meetingdate, now) = 0 and
tblEDAActivityPlanmeetingDetails.EDA_id = tblEmployee.Employee_ID) as
MyValue,
"ACC Plan Meeting" as MyCode
from tblEDAActivityPlanMeetingDetails
INNER JOIN tblEmployee on tblEDAActivityPlanMeetingDetails.EDA_ID =
tblEmployee.Employee_ID
WHERE DateDiff("d", ActivityDate, now()) = 0

above is my union query code.
The union query works fine.
My crosstab query complained that it doe not recognize the field
tblEmployee.employeeID. If I take the link out then the cross query works
fine. does crosstab query have any limitations? Your help is great
appreciated,

 

[Souris]

Thanks for the message,
Yes, I opened paramter form to enter my parameter for the crosstab parameter.
Crosstab works fine. The reprot continues to ask the parameter during design
time.

 

[Souris]

Do I need do any VBA code to pass parameter to my crosstab paramter at run
time to populate the report?

Thanks again,

 

[Duane Hookom]

IMHO, parameter prompts are never appropriate user interface
http://www.tek-tips.com/faqs.cfm?fid=6763. Use a reference to a control on a
form.