Problem #1 - getting a crosstab to show more than 12 months of data across. Is this a limitation?
Problem #2 - is related in that i prefer not to use in the Qry2 field name of: Expr1: Format([CalEndDate],"mmm")
The above seems to limit me to just 12 month names like Jan, Feb, Mar etc etc
I was prefer to see: 1/31/2007, 2/28/2007, 3/31/2007. So I want to move away from mmm.
If i leave CalEndDate "as is" in Qry2, i get an error message: :This expression is typed wrong or too complex to be evaluated."
I even tried in Qry1 to make 3 extra field where i strip out 2007 and strip out the month number and then combine the 2 stripped out numbers to look like 2007-1, 2007-2, etc
but i got no where on that either. I was thinking that maybe text would work better but no go.
Here is what I have. Two Qry's. Qry2 is a crosstab using Qry1 data. Qry1 has a Date field in it. I want this Date field called CalEndDate to become the column heading in my Qry2 crosstab.
In Qry1, I have several fields but notably:
Field: CalEndDate
Criteria: Between [Start Date] And [End Date]
Then in Qry2, the crosstab, I have:
Field: Expr1: Format([CalEndDate],"mmm")
Total: Group by
Crosstab: Column Heading
In summary: i am looking to have Qry2 ask me my start and end dates and then then give me 18 months of crosstab data based on either a concatenated field like 2006-11, 2006-12, 2007-1, 2007-2 etc in proper order. or worst case, have the field show the CalEndDate for the column headings in the crosstab.
thanks for ideas.
Penwood
Problem #2 - is related in that i prefer not to use in the Qry2 field name of: Expr1: Format([CalEndDate],"mmm")
The above seems to limit me to just 12 month names like Jan, Feb, Mar etc etc
I was prefer to see: 1/31/2007, 2/28/2007, 3/31/2007. So I want to move away from mmm.
If i leave CalEndDate "as is" in Qry2, i get an error message: :This expression is typed wrong or too complex to be evaluated."
I even tried in Qry1 to make 3 extra field where i strip out 2007 and strip out the month number and then combine the 2 stripped out numbers to look like 2007-1, 2007-2, etc
but i got no where on that either. I was thinking that maybe text would work better but no go.
Here is what I have. Two Qry's. Qry2 is a crosstab using Qry1 data. Qry1 has a Date field in it. I want this Date field called CalEndDate to become the column heading in my Qry2 crosstab.
In Qry1, I have several fields but notably:
Field: CalEndDate
Criteria: Between [Start Date] And [End Date]
Then in Qry2, the crosstab, I have:
Field: Expr1: Format([CalEndDate],"mmm")
Total: Group by
Crosstab: Column Heading
In summary: i am looking to have Qry2 ask me my start and end dates and then then give me 18 months of crosstab data based on either a concatenated field like 2006-11, 2006-12, 2007-1, 2007-2 etc in proper order. or worst case, have the field show the CalEndDate for the column headings in the crosstab.
thanks for ideas.
Penwood
