Hi Cindy,
I guess your unbound control has an expression such as:
="Report for dates between " & [Enter Report Start Date] & " and " &
[Enter Report End Date]
as its control source.
If the fields in this expression are not the same as the parameters in the
underlying crosstab, you would expect the report to prompt for them - that's
what normally happens. But since the report is based on a crosstab query,
which requires parameters to be explicitly declared, you must declare them
in the parameter clause of the underlying crosstab, even if they are not
used in a where clause in the query. When I test this situation (a declared
parameter in the crosstab, and an undeclared prompt parameter in the
report's header), I get the error message "The Microsoft Jet database engine
does not recognize '' as a valid field name or expression." Is that the
error you are seeing?
If so, the solution should be to declare the prompt fields in the query's
parameter list, or (probably better, since it eliminates the potential
problem of dates entered manually when the report opens not matching the
dates used in the criteria for the report's query) use the existing
parameters from the query in your unbound control.
Again, HTH,
Rob
Cindy said:
Hi Rob:
The crosstab query is fine and the so is the date parameter in the query
for
a date range to display records in that date range when running the query,
however, my problem is for displaying a date range in the report header
based
on this underlying crosstab query. I have been using an unbound control as
stated below on all other reports and it works fine. The date is for
display
purposes only.
Therefore, when running the report which runs the underlying query, they
input a start date and an end date for the records to be displayed and
then I
want them to enter a report start date and report end date to display at
the
top of the report in the header. This procedure works on reports with
underlying regular select queries, but not with underlying crosstab
queries.
Any further suggestions would be greatly appreciated.
Thanks
Cindy
Rob Parker said:
Hi Cindy,
You don't say what exactly it is that is not working. I suspect that
it's
not the display of the dates in the page header, but the crosstab query
which should be using these dates as criteria. If it's the latter, then
the
problem is likely to be that you have not declared the parameters for the
crosstab query - that is required for crosstab queries, but not for other
types of queries.
The SQL of your crosstab should start as follows:
PARAMETERS [Enter Report Start Date] DateTime, [Enter Report End
Date]
DateTime;
TRANSFORM ...
You can enter this directly in the SQL view of the query, or use the
Parameters item on the Query menu when the query is open in design mode
to
enter the parameters and their datatypes.
HTH,
Rob
I have a report created based on a crosstab query and I want to be able
to
type in a start date and an end date to display in the Page header of
the
report. I have this working in all other reports, except the ones
based
on a
crosstab query. Does anyone out there have any ideas? I have used an
unbound control with the following and it works on all
other reports except where it's source is a crosstabl query: ="From
"&[Enter
Report Start Date]&" To "&[Enter Report End Date]
