If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SELECT works with parameter query but CROSSTAB doesn't?
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 |
#2
|
|||
|
|||
SELECT works with parameter query but CROSSTAB doesn't?
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. "Howard" wrote in message ... 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 |
#3
|
|||
|
|||
SELECT works with parameter query but CROSSTAB doesn't?
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 .. "Howard" wrote in message ... 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. "Howard" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|