Crosstab criteria

[Guest]

I have created a cross-tab query and I want to use a form to define the
criteria of one of the fields. This field does not form part of the row
heading, the column heading or the value. However when I create this query
the following message is displayed:-

The Microsoft Jet database engine does not recognize
<[Forms]![AllSchemes]![DateSelect]> as a valid field name or expression.

Can you help?

 

[John Spencer]

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

 

[Guest]

Forms and reports expect a field to be there when you create them. One way to
fix this problem is to open the crosstab query in design view; right click in
the area near the tables; and select Properties. Next go into the Column
Headings and put in something like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.

Of course this only works if you always know what your column heading need
to be. If not, there are ways to dynamically create the column headings and
controls on the form/report. Ask again if you need that solution.

 

[Guest]

Thanks for this, but is meant by the parameter of another query? And what is
their exact name?

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

I have created a cross-tab query and I want to use a form to define the
criteria of one of the fields. This field does not form part of the row
heading, the column heading or the value. However when I create this query
the following message is displayed:-

The Microsoft Jet database engine does not recognize
<[Forms]![AllSchemes]![DateSelect]> as a valid field name or expression.

Can you help?

 

[John Spencer]

The exact name in your case is
[Forms]![AllSchemes]![DateSelect]

That is the parameter. Easiest way to do this is.
Open the query in SQL view
Type the following at the beginning of the query

Parameters [Forms]![AllSchemes]![DateSelect] DateTime;



You can use a query as the source for another query.
Parameter of another query means that if the crosstab query uses another query,
then you must also declare any parameters in the other query.

Thanks for this, but is meant by the parameter of another query? And what is
their exact name?

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

I have created a cross-tab query and I want to use a form to define the
criteria of one of the fields. This field does not form part of the row
heading, the column heading or the value. However when I create this query
the following message is displayed:-

The Microsoft Jet database engine does not recognize
<[Forms]![AllSchemes]![DateSelect]> as a valid field name or expression.

Can you help?