Report on Cross-tab Query 1

[JOEYB99]

I'm using Access 2007 and have successfully created a cross-tab query (due mostly to help from this forum) that displays dates for columns. However, this data is dynamic and I'm not sure how to create a report, or output of some kind, that is dynamic and consistent. The rest of the data is fairly static.

But I'm confused. Will I have to re-create the cross-tab query everytime the data changes?

Should I try a different approach?

Any input or suggestions would be appreciated.

 

[dhookom]

Check out faq703-5466 which you should be able to modify for dates rather than months.

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

Thanks Duane, for the speedy reply!

I just have one question regarding your FAQ703-5466, if you don't mind.


"Set the with menuing: Query|Parameter".... Please clarify.

 

[dhookom]

Set the parameter data type with the menu: Query|Parameter
[tt][blue] Forms!frmA!txtEndDate Date/Time[/blue][/tt]

I have updated the FAQ.

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

Thanks Duane, but I'm struggling with applying your FAQ.

Any help would be greatly appreciated.

I have input/modified everything as per your FAQ. When I run the Cross-tab query I get a parameter prompt for the txtEndDate. Why is that? what am I doing wrong here?

No matter what I answer for this prompt I get the same error message "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to the variables."

 

[dhookom]

Please come back minimally with your SQL statement.
Did you create a form with a text box named txtEndDate? Is the form open? Did you enter a date into the text box on the form? What is the name of the form?

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

Here is my SQL statement for the cross-tab query,

PARAMETERS [Forms]![frmA]![txtEndDate] DateTime;
TRANSFORM Sum(qryUnionQueryTransactions.PlannedQty) AS SumOfPlannedQty
SELECT qryUnionQueryTransactions.FormattedCostCode, qryUnionQueryTransactions.type, Sum(qryUnionQueryTransactions.PlannedQty) AS [Total Of PlannedQty]
FROM qryUnionQueryTransactions
GROUP BY qryUnionQueryTransactions.FormattedCostCode, qryUnionQueryTransactions.type
PIVOT "Dates" & DateDiff("d",[TransDate],"Forms!frmA!txtEndDate") In ("Dates0","Dates1","Dates2","Dates3","Dates4","Dates5","Dates6","Dates7","Dates8","Dates9","Dates10","Dates11","Dates12","Dates13","Dates14","Dates15");

Yes, I created a form called frmA. No, this form is not open when I run my cross-tab query or the cross-tab report. Should it be open?

I created a text box inside the form and named it txtEndDate.

I did not enter a date into this text box. What date should I enter into it?

 

[dhookom]

There are a number of differences between the FAQ and your attempt:
FAQ
"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
Your attempt
"Dates" & DateDiff("d",[TransDate],"Forms!frmA!txtEndDate")
Get rid of the quotes around the Forms!frmA!txtEndDate

Mth0 is sales from the same month as the ending date on your form suggests that you enter the ending date of your desired columns in the text box. I would not have suggested you create the form with a control unless I had a purpose for it. Your query would display the 15 dates before the date entered into the text box on the form as well as the entered date.

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

Thank you for the prompt reply Duane.


I am using "d" instead of "m" as per your post dated 26-NOV-12 12:00. I want actual dates to appear as columns in my cross-tab report, not months.

When I remove the double quotes from Forms!frmA!txtEndDate it then changes automatically to [Forms]![frmA]![txtEnddate] and I am still prompted for a date. Why is that?


"...suggests that you enter the ending date of your desired columns in the text box." How do I know the ending date of my desired columns here? Please elaborate.

 

[dhookom]

Again "Is the form open? Did you enter a date into the text box on the form?"

What range of dates to you want for your columns?

As explained in the FAQ, you will not get columns in the crosstabs that have actual dates as column headings. You will have columns with headings like "Dates0" to "Dates15". The FAQ explains how these generic columns will display in your report with the actual dates as headings.

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

I have opened the form called frmA, and I have manually entered a date of 12/31/2012 since I'm not clear as to what specific date should be entered here. (I asked you to please elaborate on my confusion here.)

Then I re-ran my cross-tab query and did not get a date prompt. However, I got the same error message, "This expression is typed incorrectly...expression to variables".

If I leave the txtEndDate empty or blank, then open the form, and then re-run my cross-tab query I am prompted for a date. If I provide an end date my query stops and provides the same error message, "This expression typed incorrectly... expression to variables."

How can I find out which expression is typed incorrectly?



The range of dates that I want for the columns of the report is ALL dates. I realize this could make it a long and cumbersome report but this is what the users want, at the moment.

I do understand the column headings in the FAQ. The formula that must be entered into the control source of the text boxes representing the column labels makes sense to me.

 

[dhookom]

A report will only have the horizontal space for a specific number of columns unless you have a printer capable of printing on news paper page size or greater. Most of us are limited to 8", 10.5", 13.5", or 16.5".

Your crosstab SQL statement suggests you want 16 date columns. If you want your report headings to be:

[pre]
12/1/2012 12/2/2012 12/3/2012 12/4/2012 12/5/2012 ... 12/16/2012
--------- --------- --------- --------- --------- ----------
[/pre]
You would open the form and enter 12/16/2012 into the text box. You would then open your query to see columns with headings of:
[pre]Dates0 Dates1 Dates2 Dates3 Dates4 Dates5 Dates6 Dates7 Dates8 Dates9 Dates10 Dates11 Dates12 Dates13 Dates14 Dates15
[/pre]

Having these columns in your query is your objective so you can create your report with static column names. The [Dates0] column will contain the Sum(qryUnionQueryTransactions.PlannedQty) values for 12/16/2012. The values for 12/15/2012 will be in Dates1. The 12/1/2012 values should be in the column Dates15 (15 days earlier then the ending date).

Your report will use text boxes as suggested in the FAQ to generate column headings that match your desired dates.

Duane
Hook'D on Access
MS Access MVP