Date Range in a Crosstab Report

[Tivoli0]

Hello great group,
I'm using Access97 & Win95
I have a report that is based on a crosstab query. I have no problem to get the report with ALL records. However, I'm trying to get a date range so the report will print from/to specific dates and that's where I'm having problems.
The SQL in the crosstab query (named List Objectives_Crosstab) is:

TRANSFORM Count([To Do].AutoNum) AS CountOfAutoNum
SELECT [To Do].Objective
FROM [To Do]
WHERE ((([To Do].Completed) Between [Start Date] And [End Date]))
GROUP BY [To Do].Objective
PIVOT [To Do].Goal;

The Report's Record Source points to "List Objectives_Crosstab" query above. Now, when I run the report, I get a message:
"The Microsoft Jet database engine does not recognize '[Start Date]' as valid field name or expression". The same error comes up when I tried to run the query from the query design window (Datasheet View).
So I added to the PARAMETER box the Start Date and End Date with Date/Time data type and the SQL code looks like this:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Count([To Do].AutoNum) AS CountOfAutoNum
SELECT [To Do].Objective
FROM [To Do]
WHERE ((([To Do].Completed) Between [Start Date] And [End Date]))
GROUP BY [To Do].Objective
PIVOT [To Do].Goal;

When I re-run it now from the query design window (Datasheet View), I get a prompt to enter Start Date and End Date and all is fine. However, when I try to run it from the Reports Tab (Preview), I get the prompt to enter the Start Date and End Date but after that I get a new error:

"The Microsoft Jet database engine does not recognize 'List Objectives-Crosstab' as valid field name or expression"

What am I doing wrong?

Thanks much for any help! -Ami

 

[JerryDennison]

Have you tried creating another query to limit the recordset then basing your crosstab on this query?

 

[Tivoli0]

Hi Jerry,
Thanks for your reply.
Yes I did. Getting the same thing! Unless you have any specific idea/instruction.
-Ami

 

[RichUK]

1. Remove the Order By clause on the property sheet for the report if it's there.
2. Add the date field to the crosstab again and set the parameters on that field put where on the Total grid for that column. Order By on the first date field.
HTH

 

[Tivoli0]

RichUK,
The Order By clause on the property sheet for the report was/is set to non.
I added the "Completed" date field to the crosstab again and set the parameters on that field in the criteria grid to "Between [Start Date] And [End Date]" after selecting the Where clause on the Total grid for that column.
Regarding the "Order By on the first date field", do you mean I should put "Completed" in the property sheet for the report? What do you actually mean by "the first date field"? I have only one date field called "Completed". The [Start Date] and the [End Date] are Not existing fields, I just added them to get the user prompted to specify the range.
Thanks again, --Ami

 

[rgomez1999]

I had a similiar problem and found a possible solution on Microsoft's website. Here's the link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209778

 

[Tivoli0]

Hi rgomez1999,
I tried to get the article [Q209778] but it's no longer available. Do you have another source?
Thanks for your help! -Ami

 

[67peshawar294]

I'm not sure if this will help or not. I have been using Access since it came out. I can't understand why so much code needs to be used?
Wouldn't it be simpler to create a query with the fields needed and in the criteria for the date enter;
"Between [Enter start date] And [Enter end date]"
without the quotes.
Create a crosstab query based on the other query.
Make a report and make the record source the crosstab.
Jim

 

[RichUK]

What problem do you have using the solution I gave you? [Completed] should be in your grid now twice, with no criteria on the first. Does the crosstab display when opened on its own?

 

[Tivoli0]

RichUK & Jim,

I did what both of you suggested but the problem still the same:

When running the query from the query design window (Datasheet View), I get a prompt to enter Start Date and End Date and all is fine.
But - When I'm trying to run it from the Reports Tab(Preview), I get the prompt to enter the Start Date and End Date and instead of getting the resulted report I get after that a new error msg:

"The Microsoft Jet database engine does not recognize 'List Objectives_Crosstab' as valid field name or expression"

It looks like what I need is to run the first query that captures the range I need and then to run the x-tab one, but I don't know how to do that. It might work.

I really do appreciate your efforts and advise, perhaps I should give up as it is becoming an annoying issue for me.

Thanks alot to you all !!! -Ami

 

[67peshawar294]

Hi Ami-
I don't mean to barge in but I just think it is easier to use queries to do some of this.
I had the same error msg if one of your fields on the report does not contain any values then the error msg will show up. I have to enter a dummy value to overcome this msg and then it works fine.
My report is set for three drs and the totals for glasses sold for the previous week. If one of the Drs is on vacation then there is no value for his field. Check the fields on your report and see if there is an entry for each for the specified time.
Jim

 

[RichUK]

Don't give up. You can send it to me and I'll take a look for you. The db needs to be zipped and in A97, please make sure you compact it first.
richiet@lineone.net

 

[Tivoli0]

Hi Jim,

Your reply might shade some light on the issue. You say:

"I had the same error msg if one of your fields on the report does not contain any values then the error msg will show up"

Well, I certainly have fields that contain no data! In that case, how would I overcome the err msg WITHOUT putting "something" as you had found. I DO want the query/report to count them.

Still hoping, before sending the database to our friend RichUK for inspection.

Thanks for your insights! -Ami

 

[67peshawar294]

Ami,
The msg I get is specific to a field and not to the entire crosstab query. As long I have one entry for each dr for the specified time the error does not appear.
Does the query run OK from the queries tab? Can you enter the beg and end date and the data is displayed?
If that is the case I would redo the report from scratch and see if that solves it.
Jim

 

[Tivoli0]

Hi Jim,
Thanks for your continuous support!
"Does the query run OK from the queries tab?"
Yes, and beautifully!

"Can you enter the beg and end date and the data is displayed?"
Yes, again.

"If that is the case I would redo the report from scratch and see if that solves it."
Well, I had tried that too and it gave me the same err, however, I'll try to do it again.

Hope to give you better news, and THANKS!, again -Ami

 

[Tivoli0]

Hooray!

I got a tip from Delores @(dmh64@mts.net).
She suggested I should put the report's headings in the query's properties and, what do you know, it worked!!!

Thanks, folks, for your help and support. I hope someone may benefit from Dolores' tip as well.

Have a great weekend - I do

- Ami