Pass standard query Criteria to Crosstab...

[dodgyone]

I have a standard query and a crosstab query is based on the result of the standard query. The crosstab is displayed as a subform on a form for viewing purposes etc.

In the standard query is a date column and this is always changing so the user is asked to enter the date before the query gives the results (i.e. the appropraite colums have been edited so that it asks for the date as it opens).

The problem is that the crosstab query is obviously not being updated as the dates in the standard query are not fixed. So, the form which displays the crosstab query does not give any results.

I have tried using a text box in a form, passing the date from there to the query and then opening the form with the crosstab result but no joy.

Has anybody got any suggestions?

Thanks,

Marcus

 

[JUANCARLOS]

The crosstable cant update with a criteria you must based your standard query in a table, the solution are:

1.- Create a query that create a table.
2.- Based your crosstable in that query.
3.- with code execute that table create query, and in the subform, execute the command docmd.requery.

Any other explanation.

 

[kaniz]

Juan Carlos did you get your query working, if you did would you mind sharing how did you do it. I am working on something similar and I can't seem to put a criteria on a crosstab.

 

[lameid]

I just had a similar problem... After perusing some messages I came up with a different solution.
In my case I am using a field based off a date in the underlying query:
Mnth: Format([DateField],"mmm&quot

In this case I was able to set the column headings property of the Mnth field:
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"
Defining all the column headings this way, I had no problems despite the criteria refrencing controls on a form.

Additional keywords:
Error:
The Microsoft Database Engine does not recognize '[Forms]![Form]![Control]' as a valid field name or expression.

Crosstab subquery sub query date criteria

 

[jedrtd]

So how did you get it to work, I'm getting the not recognize error on my crosstab