Cross Tab Query and a Date Form

[T Kelley]

I use Date Forms a lot to pass dates through to queries, but for some reason
I can't do the same for Cross Tab Queries. Any ideas?

 

[KARL DEWEY]

You have to declare parameters in a crosstab query.
OPen the query in design view, click on menu QUERY - Parameters. Enter the
parameter and type - it must be typed exactly the same - suggest copying and
pasting to eliminate errors.

 

[T Kelley]

I have never used that before, can you elaborate a bit more on what should go
in the Parameter Field? I am guessing the Data Type is a Date/Time.

 

[KARL DEWEY]

Copy the parameter/criteria from your query such as --
[Forms]![MyDateForm]![Start_Date]

 

[T Kelley]

Perfect! Thank You!

Copy the parameter/criteria from your query such as --
[Forms]![MyDateForm]![Start_Date]
--
KARL DEWEY
Build a little - Test a little

I have never used that before, can you elaborate a bit more on what should go
in the Parameter Field? I am guessing the Data Type is a Date/Time.

 

[T Kelley]

Here is what i have now, i found an old access reference book. What if my
query spans across more than 1 year, how can i show the results

PARAMETERS [Forms]![F DATE FORM]![STARTDATE] DateTime, [Forms]![F DATE
FORM]![ENDDATE] DateTime;
TRANSFORM Count([T PAID TW VARIANCE DATA NEW].SC_ID) AS CountOfSC_ID
SELECT [T PAID TW VARIANCE DATA NEW].GROUP_ID
FROM [T PAID TW VARIANCE DATA NEW]
WHERE ((([T PAID TW VARIANCE DATA NEW].SC_DT) Between [Forms]![F DATE
FORM]![STARTDATE] And [Forms]![F DATE FORM]![ENDDATE]))
GROUP BY [T PAID TW VARIANCE DATA NEW].GROUP_ID
PIVOT Format([SC_DT],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

What does the pivot line have to say to See Jan, Feb Mar etc 2007 than go to
2008.

My date range is 1/1/2007 thru 4/30/2008.

Copy the parameter/criteria from your query such as --
[Forms]![MyDateForm]![Start_Date]
--
KARL DEWEY
Build a little - Test a little

I have never used that before, can you elaborate a bit more on what should go
in the Parameter Field? I am guessing the Data Type is a Date/Time.

 

[KARL DEWEY]

Modify the query like this ---
........
SELECT [T PAID TW VARIANCE DATA NEW].GROUP_ID, Format([SC_DT],"yyyy") AS
[Var Year]
.......
GROUP BY [T PAID TW VARIANCE DATA NEW].GROUP_ID, Format([SC_DT],"yyyy") AS
[Var Year]
........

Or if you want the year then ID ---
........
SELECTFormat([SC_DT],"yyyy") AS [Var Year], [T PAID TW VARIANCE DATA
NEW].GROUP_ID
.......
GROUP BY Format([SC_DT],"yyyy") AS [Var Year], [T PAID TW VARIANCE DATA
NEW].GROUP_ID
........
--
KARL DEWEY
Build a little - Test a little

Here is what i have now, i found an old access reference book. What if my
query spans across more than 1 year, how can i show the results

PARAMETERS [Forms]![F DATE FORM]![STARTDATE] DateTime, [Forms]![F DATE
FORM]![ENDDATE] DateTime;
TRANSFORM Count([T PAID TW VARIANCE DATA NEW].SC_ID) AS CountOfSC_ID
SELECT [T PAID TW VARIANCE DATA NEW].GROUP_ID
FROM [T PAID TW VARIANCE DATA NEW]
WHERE ((([T PAID TW VARIANCE DATA NEW].SC_DT) Between [Forms]![F DATE
FORM]![STARTDATE] And [Forms]![F DATE FORM]![ENDDATE]))
GROUP BY [T PAID TW VARIANCE DATA NEW].GROUP_ID
PIVOT Format([SC_DT],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

What does the pivot line have to say to See Jan, Feb Mar etc 2007 than go to
2008.

My date range is 1/1/2007 thru 4/30/2008.

Copy the parameter/criteria from your query such as --
[Forms]![MyDateForm]![Start_Date]
--
KARL DEWEY
Build a little - Test a little

I have never used that before, can you elaborate a bit more on what should go
in the Parameter Field? I am guessing the Data Type is a Date/Time.

:

You have to declare parameters in a crosstab query.
OPen the query in design view, click on menu QUERY - Parameters. Enter the
parameter and type - it must be typed exactly the same - suggest copying and
pasting to eliminate errors.
--
KARL DEWEY
Build a little - Test a little


:

I use Date Forms a lot to pass dates through to queries, but for some reason
I can't do the same for Cross Tab Queries. Any ideas?