Cross-tab query parameters: asking, and asking, and asking, and asking...

[andreas_udby]

I have a report that is being populated by data in a cross-tab query. Because I need to have the user specify the date range for the report, I have a criterion in the query that reads "Between [Enter Start Date] And [Enter End Date]". This has worked fine in all my other queries and reports, but I couldn't make it work for the cross-tab query. Then I read about defining the parameters to make that kind of operation work.

So I went in and defined [Enter Start Date] as Date/Time and [Enter End Date] as Date/Time. Fine.

But now, whenever I enter the date range, I get the "Enter Parameter Value" message box three or four times for both the start date and end date inputs. Sometimes it will even pop up while I have the report in Design mode. No reason why -- it just seems to pop up.

I've even tried building a second query just to limit the date range and have that one feed the cross-tab, but no luck; it still does the same thing. Does anyone have an idea as to what I've done wrong here? Is there some more straightforward way of tackling this?

Thanks,
Andreas

 

[jzwp22]

What I have done in the past is to create an unbound form where the user can input the start and end dates. Add a command button to form to open your report. As long as the form is open, Access will go to the form to get the dates without repeated requests.

You will have to adjust your query to reference the form controls

From this: Between [Enter Start Date] And [Enter End Date]

to this: Between Forms!formname!startdatecontrolname and Forms!formname!enddatecontrolname

You will also have to modify the PARAMETER Clause of the query as well.

 

[vbaInet]

But now, whenever I enter the date range, I get the "Enter Parameter Value" message box three or four times for both the start date and end date inputs. Sometimes it will even pop up while I have the report in Design mode. No reason why -- it just seems to pop up.

Your crosstab query may be pulling from another query that implements parameters.

 

[boblarson]

First, you need to use a query to supply the parameters (not a cross-tab).

Second, you create the cross tab from that query.

Third, you define the parameters in BOTH queries, even though the parameters are in the first.

 

[andreas_udby]

@ jzwp22: That's a good thought; I may have to try that.

@ vbaInet: I went back and checked, and those parameters are only in the cross-tab query. It was a good thought, though.

@ boblarson: Okay... I think I'm following you. Let me give that a shot and we'll see how it goes.

Thanks for everyones' replies!

 

[HiTechCoach]

As Bob as correctly potint4ed out, you must define parameters. I would suggest using a form to collect the dates.

See:
Dynamic Report based on a Crosstab query
I import this report into every data where I use a cross tab query. It is very easy to reuse.
.

 

[AWentz]

Thank you to Boblarson. I've worked on a cross-tab parameter query linked to a report that works off a date range on a form and was getting the same types of errors. Tried all types of ideas from the web but Bob's comment on this thread was the only thing that worked. I can call it a day now!