Crosstab Query parameter problem

[joe31709]

PARAMETERS [Forms]![frmMylaunchForm1]![sdate] DateTime, [Forms]![frmMylaunchForm1]![Edate] DateTime;
TRANSFORM Sum(Time.[OVER TIME]) AS [SumOfOVER TIME]
SELECT Time.[Last Name]
FROM [Time]
WHERE (((Time.DATE) Between [Forms]![frmMylaunchForm1]![sdate] And [Forms]![frmMylaunchForm1]![Edate]))
GROUP BY Time.[Last Name]
PIVOT Time.DATE;

I have a form setup to enter dates in, it was a combo box but I changed it back to a text box.

When I put info in the text box and hit the run query button , it still asks me for Enter Parameter Values [Forms]![frmMylaunchForm1]![sdate] and i can enter it and then it asks for [Forms]![frmMylaunchForm1]![edate] and i can enter that also.

It works but shouldnt i just be able to hit the run query button and not have to enter the info twice?

 

[Pat Hartman]

Your form must be open when you run the query. Or, if the form is open, the problem is that the form field references are incorrect.

 

[jmersing]

You know I was going to post this same problem the other day. I found a workaround but, I spent an hour checking syntax, control names and field names to no avail. I actually removed the Trasform part of the query and just ran it as a Select query off the same form fields and it worked? Proving there was no incorrect reference. Not that I am disputing what Pat posted, if you have the slightest error in your field or control names it will not work.
But I was really dumfounded, I was thinking the form was corrupt?

I'm wondering if it is a bug?


Jim

 

[joe31709]

After I hit the Run Query button I put on there it pops up the parameters but there is exclamation points in between everything. Is that correct?

It looks like this... After I fill in the text boxes and hit the button I get
Forms!frmMylaunchForm1!Sdate ( I can put the info in there and hit enter)
Forms!frmMylaunchForm1!Edate (same as before )

and the query runs fine.

my form is called frmMylaunchform1 Text boxes are Sdate and Edate.

PARAMETERS [Forms]![frmMylaunchForm1]![Sdate] DateTime,[Forms]![frmMylaunchForm1]![Edate] DateTime, [Forms]![frmMylaunchForm1] Text ( 255 );

Should there be !'s ?

 

[Pat Hartman]

Your last parameter isn't referencing a control.

jmersing,
Crosstab queries will not work properly if parameters are not specifically defined.

Whenever your parameters are form fields, the form MUST be open when the query is run.

 

[jmersing]

It's working now Pat:

PARAMETERS [forms]![employees list]![startdate] DateTime, [forms]![employees list].[endDate] DateTime;
TRANSFORM Max(qryNewestReport.Amounts) AS Amounts
SELECT qryNewestReport.Code, qryNewestReport.Function, qryNewestReport.Plan, qryNewestReport.Location, qryNewestReport.Type
FROM qryNewestReport
where qrynewestreport.week >= [forms]![employees list]![startdate]
and qrynewestreport.week <= [forms]![employees list].[endDate]
GROUP BY qryNewestReport.Code, qryNewestReport.Function, qryNewestReport.Plan, qryNewestReport.Location, qryNewestReport.Type
PIVOT Format([Week],"Short Date");

Thanks!