Adding date parameter to report based on crosstab query 1

[ISO9000Man]

I have built a report on a summary query built on a crosstab query to get the information formatted as I need. The report is exactly the way I want it. However I can't seem to find the way to apply a date filter so that I can input the date range to see only the records from that date range in the report. Can anyone help?

Thanks...Russ

 

[dhookom]

Add the criteria like you would any other query and then select Query|Parameters and enter the exact parameters and their data types.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ISO9000Man]

I tried that:

Between [Start Date] And [End Date]

and I still get the following error:
"The Microsoft Jet database engine does not recognize '[Start Date]' as a valid field name or expression."

Am I using the wrong expression?

 

[dhookom]

Maybe my reply wasn't clear enough. I expected you to select the menu in design view Query|Parameters and then enter your parameters and their types:
[Start Date] Date/Time
[End Date] Date/Time




Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

Hi, this post is helping me get closer to what I am trying to achieve, and I am hoping you might be able to help me.

Let me explain my data. I link an sql table into access. This is a live table of raw data that is constantly updating. I am counting the data on 2 columns of groupings. These are basically records of every phone call. I do a count on callid. i group on the type of call and the "call center" it was placed from.

Here is the deal. I am trying to figure out how to take this, as I have it in a cross tab, and pass date criteria to it. it will be passed as text because the field is like 20040130. Not good, but thats how we get it.

Anyway. I tried your parameter suggestion, but I dont see it making that parameter for the date field.

here is how the crosstab appears

calltypelocal calltypeinternational etc
place1 5 22 4
place2 14 41 9
place3 you get idea

I am trying to get this all to a report. The user will need to specify the dates to view this for. I tried to do a parameter in the first query, the one that feeds the crosstab, but the crosstab wont take it. I got the same error as the original poster. So here I am, I tried this parameter thing, but it isnt pointing to my date field, so no matter what I put in the [startdate] and [enddate] parameters, it gives me the same data.

Please help?

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

You must include the prompts in the criteria.

Between [startdate] and [enddate]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

huh? If I put the date field into the design of the query, it says that it cant recognize the [startdate] thing.

here is the sql that is not working at this time:

PARAMETERS((step1.xDate) [start date] Text ( 255 ), [end date] Text ( 255 ));
TRANSFORM Sum(step1.CountOfxCall_ID) AS SumOfCountOfxCall_ID
SELECT step1.[Name Field], Sum(step1.CountOfxCall_ID) AS [Total Of CountOfxCall_ID]
FROM step1
GROUP BY step1.[Name Field]
PIVOT step1.Call_Type;


Syntax error in parameter clause



misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

Try:
PARAMETERS [start date] Text ( 255 ), [end date] Text ( 255 );
TRANSFORM Sum(step1.CountOfxCall_ID) AS SumOfCountOfxCall_ID
SELECT step1.[Name Field], Sum(step1.CountOfxCall_ID) AS [Total Of CountOfxCall_ID]
FROM step1
WHERE xDate Between [Start Date] AND [End Date]
GROUP BY step1.[Name Field]
PIVOT step1.Call_Type;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

It says " The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain to many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

I copy and pasted your exact sql...

:-(

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

Is Step1 a table or query that contains the fields CountOfxCall_ID (numeric), xDate, Call_Type, and [Name Field]? Is the xDate field text or numeric or date?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

step1 is a query, and xDate is a long integer number. Thank you for all of your help. I appreciate your time in helping me to troubleshoot this issue.

After this, I get to figure out how to put the month of the report, on the report, and then save it as a static report, for historical reference! YAY!

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

Earlier you stated "it will be passed as text" and now you state "long integer number" so I assume you changed your parameter data types and this is now working.

To get the month of the report on the report, add a text box with a control source of:
=Mid(Trim(Str([Start Date])),5,2)
If you need a month name, try
=MonthName(Mid(Trim(Str([Start Date])),5,2))
You can save a report to either a snapshot or pdf or jpg with some of these formats requiring additional software.

If your current questions are satisfied, I would suggest you start a new thread regarding outputting a static view of your report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

Thanks, I actually found out that it wasnt text, as I had assumed it was. I am working on your Month thing now, using the =MonthName(Mid(Trim(Str([Start Date])),5,2))

One question, and I will end this thread...

Is there a way to make it show as Month, Year? like 200401 would show as January, 2004. I am working on this now too.

Thanks a bunch

misscrf

Management is doing things right, leadership is doing the right things

 

[misscrf]

ok, I got a problem with the =MonthName(Mid(Trim(Str([Start Date])),5,2))


This report/query runs off of 2 parameters. [start date] and [end date] If I use the above, I just get the first month for every group heading. if I use the control source [date] I get #error.



misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

group heading"? "control source [date]"?
I don't recall anything about group headers or a field named date.

Please try to think through your requirements and include data types, desired output, sample data values,...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

ok we have this crosstab query. It looks like:

calltypelocal calltypeinternational etc
place1 5 22 4
place2 14 41 9
place3 you get idea


In the back end we took the field xDate, which is a long integer number, added it to the query, and did this to it: Date: Left$(xDate,6) This took 20040101 and made it 200401. This allowed the data to group by month instead of day. Now, I have parameters, as you instructed, for this field. The parameters are Between [start date] and [end date]

On the report, I am trying to take the crosstab data, and add a group heading for the date, so that the data shows grouped by month, year. The control source for that group or for the date field is [Date] Does that make sense?

I'm sorry, I'm horrible at explaining things!

Thanks again for all of your help.


misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

You can add [Start Date] and [End Date] as Row Headings in your crosstab and choose "Group By"
[tt]
Field: [Start Date] [End Date]
Crosstab: Row Heading Row Heading
Total: Group By Group By
[/tt]
This will make the two values available in your report. Once you have the values, you can use the Mid() and other functions to extract whatever you want.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

ok this is getting me closer. Here is the thing though. On the report, I am trying to group on month. so my question now becomes, how do I do a group on the date field, and have a field in the grouping that will show the date for each group? For example, I may put 200406 for the start date, and 200410 for the end date. I will want the report to look something like this:

June, 2004

calltypelocal calltypeinternational etc
place1 5 22 4
place2 14 41 9
place3 you get idea


July, 2004

calltypelocal calltypeinternational etc
place1 5 22 4
place2 14 41 9
place3 you get idea


August, 2004

calltypelocal calltypeinternational etc
place1 5 22 4
place2 14 41 9
place3 you get idea


and so on through October, 2004

My concern are the month, year fields being shown...

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

I assume you have a field/column in your crosstab that looks like 200406, 200407, 200408,... I think you gave this field the name [Date] which I would avoid since it is the name of a function. However, your group header could contain the expression:
=MonthName(Right([date],2)) & ", " & Left([Date],4)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

sorry,

when I put this: =MonthName(Right([MyDate],2)) & ", " & Left([MyDate],4)

It says that the Microsoft Jet Engine does not recognize " as a valid field name or expression.



misscrf

Management is doing things right, leadership is doing the right things