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]

Where did you put the expression? Is this in a control source of a text box in the report? Do you have a column in your report's record source named "MyDate"?

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

 

[misscrf]

I put this in the control source of a text box, yes. This text box is in the row heading for "MyDate" Is that wrong?

misscrf

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

 

[dhookom]

And "Do you have a column in your report's record source named "MyDate"? "

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

 

[dhookom]

I clicked the submit button to fast. Can you provide the SQL view of your Crosstab Query?

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

 

[misscrf]

yes, I do....

PARAMETERS [Start Date] Long, [End Date] Long;
TRANSFORM Sum(step1.CountOfxCall_ID) AS SumOfCountOfxCall_ID
SELECT step1.[Name Field], Left$([xDate],6) AS MyDate, Sum(step1.CountOfxCall_ID) AS [Total Of CountOfxCall_ID]
FROM step1
WHERE (((Left$([xDate],6)) Between [Start Date] And [End Date]))
GROUP BY step1.[Name Field], Left$([xDate],6)
PIVOT step1.Call_Type;


Not that I should bring this up, because its just another problem.... But when I am in the design of the report, and I go to preview, I get asked for the [start date] then [end date] and then it waits a bit... then I get asked for both again.

Don't know what thats about.

misscrf

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

 

[dhookom]

A couple recommendations:
1) I never use parameter prompts. Creating controls on forms is much powerfull and flexible. I HATE responding to prompts for anything other than clicking a yes/no/cancel button. Your parameters and criteria should be something like:
Between Forms!frmDates!txtStartDate and Forms!frmDates!txtEndDate
2) If all of your column headings are known, you should enter the values into the column headings property or the crosstab:
Column Headings:"calltypelocal","calltypeinternational", etc
3) if you take out the text box =MonthName(Right([MyDate],2)) & ", " & Left([MyDate],4), do you still get the error?

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

 

[misscrf]

I was still getting that error, why is that?


misscrf

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

 

[dhookom]

I wouldn't know why you are still getting the error when you remove the text box. Does the query work without errors? If so, check the sorting and grouping properties. Then start deleting controls on your report until the error message goes away.

Did you look at my first two suggestions?

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

 

[misscrf]

I looked at your first 2 suggestions, but I am worried about how much I will be redoing everything. I will check my report again, and get rid of that error. The query does not have that error.


misscrf

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

 

[misscrf]

ok, I take that back, lol. When I take that field out, it works fine. No error. BTW, remember what I said about going from design to preview? Well, when I just open the report, it only asks for the parameters once...

yeah, I think it doesnt like that formula for some reason. I put in the ugly MyDate field in the MyDate header ,and I get 200406 and such for each month heading.

I like that, just want to get it to look better, ie June 2004.

I'm getting closer.


misscrf

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

 

[dhookom]

Make sure the name of the text box is not also the name of a field.

Also there may be an issue with converting data types or your Access version. The MonthName() function maybe a newer function than your version of Access. Try this expression and make sure the name of the text box is not a field name.

= Format(DateSerial(Left([MyDate],4),Right([myDate],2),1),"mmmm yyyy")

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

 

[misscrf]

omg, that did it! you rule! I wish I could give you a hudred stars!!!!

Want to help me with charting this now? LMAO.



misscrf

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

 

[dhookom]

misscrf,
Start a new thread with any charting questions, (please)

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

 

[misscrf]

hahaha. I will. Thanks again for all of your help!

misscrf

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