Problem with date filters on Crosstab Query
Hi All,
I am having a problem with running a report from a Form which uses a Crosstab Query to supply the criteria for the Report. The problem is that I have 2 date text boxes on the form, which I was hoping to use as a beginning and end date for the report, but I am getting an error message that it does not recognise txstart (the first date text box) as a field, even though it appears in the Query as a field. I can run the report no problem with all of the other criteria, but as soon as I add the dates, whether in the SQL statement to open the Report, or even as parameters in the Query, I get the error message.
Bill
I am having a problem with running a report from a Form which uses a Crosstab Query to supply the criteria for the Report. The problem is that I have 2 date text boxes on the form, which I was hoping to use as a beginning and end date for the report, but I am getting an error message that it does not recognise txstart (the first date text box) as a field, even though it appears in the Query as a field. I can run the report no problem with all of the other criteria, but as soon as I add the dates, whether in the SQL statement to open the Report, or even as parameters in the Query, I get the error message.
Bill
Jim P.
Can you post the SQL?
A textbox is not a field. Give the textbox the "txt" prefix, so it won't be confused with its bound field.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Might as well post it now...
In a Crosstabe query like this:
Rows=Customers
Columns= Countries
Summary=SumFreight
The SQL will something like this:
TRANSFORM Sum(tblOrders.Freight) AS SumOfFreight
SELECT tblOrders.CustomerID
FROM tblOrders
GROUP BY tblOrders.CustomerID
PIVOT tblOrders.ShipCountry;
Notice here that there is no "Field" for each individual Country (USA, Mexico, Canada, ...ect), There is only one Generic field for the Country:
PIVOT tblOrders.ShipCountry
...yet when you run the query, the Country "Fields" magically appear. (USA, Mexico, Canada, ...ect)
The same is true in a report.
If the source for the Report is the cross tab query directly, the report really wont have a Field for each country in the source, yet the Report will display these Fields when opened.
For lack of a better explanation, the Pivot statement forces the individual Countries to appear when the query is run.
What does this all mean?
If you base you report on a Crosstab query directly, the Report will be Hardcoded with the individual Field Names.
This means that if you enter criteria that will contain a new Country (or that does not reference an existing Country) and your system may result in a similar "Does Not Recognize&" error
JeffCoachman
In a Crosstabe query like this:
Rows=Customers
Columns= Countries
Summary=SumFreight
The SQL will something like this:
TRANSFORM Sum(tblOrders.Freight) AS SumOfFreight
SELECT tblOrders.CustomerID
FROM tblOrders
GROUP BY tblOrders.CustomerID
PIVOT tblOrders.ShipCountry;
Notice here that there is no "Field" for each individual Country (USA, Mexico, Canada, ...ect), There is only one Generic field for the Country:
PIVOT tblOrders.ShipCountry
...yet when you run the query, the Country "Fields" magically appear. (USA, Mexico, Canada, ...ect)
The same is true in a report.
If the source for the Report is the cross tab query directly, the report really wont have a Field for each country in the source, yet the Report will display these Fields when opened.
For lack of a better explanation, the Pivot statement forces the individual Countries to appear when the query is run.
What does this all mean?
If you base you report on a Crosstab query directly, the Report will be Hardcoded with the individual Field Names.
This means that if you enter criteria that will contain a new Country (or that does not reference an existing Country) and your system may result in a similar "Does Not Recognize&" error
JeffCoachman
ASKER
Excellent Jeff, works perfectly. Thank you very much
Bill
Bill
;-)
ASKER
Hi Jeff,
I will post another question with more points, but is there any way around the problem of 'does not recognize....' when there is no field with that name. The report is already built with the field names in it for all eventualities, but I have now come across a few where there is at least one field not in the data.
Bill
I will post another question with more points, but is there any way around the problem of 'does not recognize....' when there is no field with that name. The report is already built with the field names in it for all eventualities, but I have now come across a few where there is at least one field not in the data.
Bill
<is there any way around the problem of 'does not recognize....' when there is no field with that name.>
Not that I know of.
Again, this is the pitfall of using a Crosstab query as the direct source for a Report.
One way around this is to simply print the CrossTab query directly.
Since the Query, will be almost identical to Report anyway.
;-)
This is what I would do.
Just bear in mind that when printing a Crosstab query, or printing a Report based on a crosstab query, that, realistically, you will only be able to print 10 or so "Fields" on a sheet.
If your query results in more than 10 columns (Coutries, for example), the remaining fields will move to the next sheet. Possibly not giving you a "Clean" looking printout.
The other option is to go with a "Grouped" report.
With a grouped report the "Fields", that are normally in the columns, will be in the rows.
This means that the report will have more pages, but the issue or the "Not Recognized" error will go away.
Here is a new sample.
Note that in the new grouped Report, "Germany" is listed.
However in the crosstab report, it is missing.
(Another, pitfall of using a Crosstab query as the direct source for a Report.)
A final "Ugly" option would be to build a custom report with all the possible fields (and pray that no more fields get added, or removed. :-O )
And use agregate Functions to display the summaries.
But I don't think it is worth all the trouble.
JeffCoachman
Not that I know of.
Again, this is the pitfall of using a Crosstab query as the direct source for a Report.
One way around this is to simply print the CrossTab query directly.
Since the Query, will be almost identical to Report anyway.
;-)
This is what I would do.
Just bear in mind that when printing a Crosstab query, or printing a Report based on a crosstab query, that, realistically, you will only be able to print 10 or so "Fields" on a sheet.
If your query results in more than 10 columns (Coutries, for example), the remaining fields will move to the next sheet. Possibly not giving you a "Clean" looking printout.
The other option is to go with a "Grouped" report.
With a grouped report the "Fields", that are normally in the columns, will be in the rows.
This means that the report will have more pages, but the issue or the "Not Recognized" error will go away.
Here is a new sample.
Note that in the new grouped Report, "Germany" is listed.
However in the crosstab report, it is missing.
(Another, pitfall of using a Crosstab query as the direct source for a Report.)
A final "Ugly" option would be to build a custom report with all the possible fields (and pray that no more fields get added, or removed. :-O )
And use agregate Functions to display the summaries.
But I don't think it is worth all the trouble.
JeffCoachman
ASKER
Hi Jeff,
Thanks for the reply. There does not seem to be a file attached.
Bill
Thanks for the reply. There does not seem to be a file attached.
Bill
oops, here you go...
Access-EEQ-24577123CrossTabQuery.mdb
Access-EEQ-24577123CrossTabQuery.mdb