SELECT works with parameter query but CROSSTAB doesn't?

[Howard]

I have a simple SELECT query that extracts all fields from a table that are
within two dates displayed in text boxes on a form. ([forms]![Management
Reports Memu]![txtFromDate] and [forms]![Management Reports
Memu]![txtToDate]

Its called ExitsFilteredByDate

I do all sorts of other queries and reports using this query as a source and
they work fine but when I try to do a cross tab query using it as the source
I get an error saying tthat the jet engine does not recognise
[forms]![Management Reports Memu]![txtFromDate] as a valid field name or
expression.

The same cross tab applied to the raw base table works OK

Any ideas what is going on and how to fix it?

Howard

 

[Howard]

I think I may have found my own solution and am posting it here for others.
(if this is just s fluke then someone let me know otherwise I will just
confuse others!)

I went to the base select query and using 'parameters' identified the data
type of the parameters being drawn from the form and this seems to have
fixed it,
Odd though as I use a SELECT * and then repeat the field for the criteria
without showing it so the fields in the output containing the date that I am
selecting is the original one so to speak, not the one in which I placed the
criteria.

 

[John Spencer]

Crosstab queries require that you declare the parameter types for the query
itself and any other queries that are used by the crosstab.

It is optional for other types of queries in Access, although a handy thing
to do in some cases when Access cannot figure out the data type of the
parameter. Often if you are using a form as the parameter source and
haven't specified a format on the control, Access will mistake a date for a
math statement and instead of searching for 1/1/2006 will search for 1
divided by 1 divided by 2006 and will return no records as a match.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I think I may have found my own solution and am posting it here for others.
(if this is just s fluke then someone let me know otherwise I will just
confuse others!)

I went to the base select query and using 'parameters' identified the data
type of the parameters being drawn from the form and this seems to have
fixed it,
Odd though as I use a SELECT * and then repeat the field for the criteria
without showing it so the fields in the output containing the date that I
am selecting is the original one so to speak, not the one in which I
placed the criteria.

I have a simple SELECT query that extracts all fields from a table that
are within two dates displayed in text boxes on a form.
([forms]![Management Reports Memu]![txtFromDate] and [forms]![Management
Reports Memu]![txtToDate]

Its called ExitsFilteredByDate

I do all sorts of other queries and reports using this query as a source
and they work fine but when I try to do a cross tab query using it as the
source I get an error saying tthat the jet engine does not recognise
[forms]![Management Reports Memu]![txtFromDate] as a valid field name or
expression.

The same cross tab applied to the raw base table works OK

Any ideas what is going on and how to fix it?

Howard