crosstab report.. monthly reporting 1

[vise]

Hey All,
I created a cross-tab report which utilizes shift number grouped by row heading, line number grouped by column heading, and then # of errors as the value. I cannot add the time field in the wizard because I am using all the fields for the cross-tab. How can I make so that my report/query shows only one month at a time?
Thanks for all your help,
-vise

 

[dhookom]

If you want to filter for a month, you can add the date field to your query grid.
YrMth:Format([YourDate],"yyyymm&quot

Set the criteria to:
[Enter yyyymm]
Menu select Query|Parameters and enter
[Enter yyyymm] Text



Duane
MS Access MVP

 

[vise]

I get the following message:
Invalid bracketing of [Enter yyyymm]
for the criteria i guess..
Field: Format([DateTime],"yyyymm&quot
Crosstab: where
Criteria: [Enter yyyymm]
and i also enterd that in the parameters as a text.
Thanks.

 

[vise]

ok,
I edited and i did the following:
Parameters-> [yymm] date/time
Field: Format([DateTime],"yymm&quot
Crosstab: where
Criteria: [yymm]
It now asks me for a date, however when i input for example, 0307, it does not display that data.
Any ideas?
Thanks.

 

[dhookom]

yymm is not a date/time. It is text. I would expect that your 0307 is being interpreted as 307 days past Dec 30, 1899.

Duane
MS Access MVP

 

[vise]

so what should i mark it so that it recognizes it as a date?
Thanks,
~vise

 

[dhookom]

Do you want to enter a date or just "0307"? If you want to enter just 0307, then change the parameter type to text as I suggested. If you want to enter a date, you will need to set the criteria to
Format([Enter any Date],"yymm&quot
Then set your Query Parameter to:
[Enter any Date] Date/Time

Duane
MS Access MVP

 

[vise]

I made the changes you suggested.
Query Criteria:
Format([Enter any Date],"yymm&quot
Query Parameter
[Enter any Date] Date/Time
..
Now, I think the problem lies with the Field:
I have it set to:
Format([DateTime],"yymm&quot
Where DateTime is a field on my table that lists all the corresponding times as 7/21/03. and is a Date/Time Field.
..Is there something I could change with that to organize my data so it's only displayed by month?
Thanks a lot!
~vise

 

[dhookom]

Doesn't Format([DateTime],"yymm&quot organize your data so it's only displayed by month?

Duane
MS Access MVP

 

[vise]

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950

 

[vise]

That's what i thought it was supposed to do.. but nothing outputs in the query.
~vise

 

[dhookom]

Create a simple query with your table that contains the field [DateTime]. Add a column to the query with the field calculation:
YrMth:Format([DateTime],"yymm&quot
Run the query to see the results. Then add a criteria under this column:
Format([Enter Any Date],"yymm&quot
Run it again. Does this work as designed?

Duane
MS Access MVP

 

[vise]

Hey, The last part does not work. where I enter the criteria under the column. everything else works. but much like the last time, it will not display anything from that date. Am I doing something wrong?
~vise

 

[dhookom]

When you open the query without the criteria, you should be seeing a column of values like:
0703
0704
0805
etc
Correct?
When the query has a criteria as suggested, are you typing in a real date like 8/4/2003?

Duane
MS Access MVP

 

[vise]

yes, i am getting that data before the criteria.
No, i'm not entering a real date. im entering 0307. which is listed as a yrmnth.
Thanks,
~vise

 

[vise]

ahhh... was that all????
THANK YOU SO MUCH!
i was getting in my mind that I had to enter the month/date. ... now you wouldn't be able to help me port this data to a graph would you?
Thanks,
~vise

 

[dhookom]

Why don't you start a new thread regarding graphing your query output.

Duane
MS Access MVP

 

[vise]

k. Thanks a lot for your help. you deserve a start for that