Problem with date filters on Crosstab Query

WJReid
WJReid used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2014

Commented:
Can you post the SQL?

Top Expert 2009

Commented:
A textbox is not a field.  Give the textbox the "txt" prefix, so it won't be confused with its bound field.
MIS Liason
Most Valuable Expert 2012
Commented:
Try this:

I used functions to pull the dates into the crosstab query.

There are other concerns when creating your report directly from a crosstab query as well.
(I will post these later, if you like)

First please verify that someting like this will work for you, buy duplicating what I have done in this sample, in your database.

Study the sample carefully and thouroghly.

;-)

JeffCoachman
Access-EEQ-24577123CrossTabQuery.mdb
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Author

Commented:
Excellent Jeff, works perfectly. Thank you very much

Bill
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)

Author

Commented:
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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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

Author

Commented:
Hi Jeff,

Thanks for the reply. There does not seem to be a file attached.

Bill
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial