Cross Tab Query Criteria 1

[dynamictiger]

I have created a cross tab query which returns the results I want in the way I need them returned.

However, the cross tab needs to populate dynamically, based on an open form. When I try and add criteria being the form field I am trying to match I am told

"The Mocrosoft Jet Engine does not recognise 'my field' as a valid field name or expression"

Is it possible to use a cross tab query this way? Is there some trick I am missing?

 

[Darrylles]

Hi,

If I understand correctly, you first need to create another query to restrict the returned data using your criteria, then use this query to feed your crosstab query.

Make sure all required fields for the crosstab are 'stated' in the 1st query (not mytable.*).

(I had similar problems with crosstabs when I first used 'em).

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[hyperiest]

I have had similar difficulties with criteria and crosstab queries. If you create a base query with criteria prompts upon which to run your crosstab, you will get these errors. What you need to do is create parameters within which to run your crosstab query. You can do this either in the Query Design Grid or in SQL view by defining your parameters for the query.

In SQL view, you do this by entering your parameters at the start of the SQL statement like this:

PARAMETERS [Select Parameter] Text;

The first part tells the comp that you are entering a parameter, the part in brackets is what you want your prompt to be, and the Text states that the value entered will be in text format.

Then you need to enter the parameters, much as you would criteria. Do this after the SELECT portion of the SQL statement, before the GROUP BY, ORDER BY and PIVOT portions, like this:

WHERE [Select Parameter]=Value

If you want to query by more than one value, you need to use an InStr statement like this:

WHERE INSTR([Select Parameter],[field])>0

If you want to use multiple values or a wildcard to return all records write it like so:

WHERE INSTR([Select Parameter],[field])>0 or INSTR([Select Parameter],[field]) IS NULL

In order not to get other errors, you will also need to force column headings in your crosstab.

Hope this helps. If you need more help, let me know.

Captain_D

 

[mwolf00]

That really helped me out on another question. Here's a star. Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com