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]

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

"Cindy" <(E-Mail Removed)> wrote in message
news:B1739455-FAFC-4D10-8622-(E-Mail Removed)...
>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]
>
>

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" wrote:

> 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
>
> "Cindy" <(E-Mail Removed)> wrote in message
> news:B1739455-FAFC-4D10-8622-(E-Mail Removed)...
> >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]
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
newsFF119A2-A813-4676-A506-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>> "Cindy" <(E-Mail Removed)> wrote in message
>> news:B1739455-FAFC-4D10-8622-(E-Mail Removed)...
>> >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]
>> >
>> >
>>
>>
>>

Hi Rob, Yes, that is the error message I get as well,"The Microsoft Jet
database engine does not recognize '' as a valid field name or expression."
And when I do this in the queries parameter list it keeps asking for the
dates continuously without running the report.

Cindy

"Rob Parker" wrote:

> 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" <(E-Mail Removed)> wrote in message
> newsFF119A2-A813-4676-A506-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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
> >>
> >> "Cindy" <(E-Mail Removed)> wrote in message
> >> news:B1739455-FAFC-4D10-8622-(E-Mail Removed)...
> >> >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]
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Hi Cindy,

That seems rather strange. Are you sure there are no typos; that the
entries in the Parameter list exactly match the entries in your unbound
control? A missing or doubled-up space character could be the culprit.

If that's not the problem, would you please post the SQL of your crosstab,
and the expression from your unbound control.

Rob

"Cindy" <(E-Mail Removed)> wrote in message
news:6833D6EF-BAFC-4B37-ACF1-(E-Mail Removed)...
> Hi Rob, Yes, that is the error message I get as well,"The Microsoft Jet
> database engine does not recognize '' as a valid field name or
> expression."
> And when I do this in the queries parameter list it keeps asking for the
> dates continuously without running the report.
>
> Cindy
>
> "Rob Parker" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> newsFF119A2-A813-4676-A506-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >> "Cindy" <(E-Mail Removed)> wrote in message
>> >> news:B1739455-FAFC-4D10-8622-(E-Mail Removed)...
>> >> >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]
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>