[AccessD] another CrossTabulation question

Hi Oleg

Adjust your WHERE statement assuming you look at one calendar year
only:

WHERE (Year([Ron Data spr].[Preliminary Investigation Opened])=
Year(CDate([Forms]![Report Form]![Start_Date])))
And [PreliminaryInvestigation Opened] Is Not Null

Then, in your PIVOT statement, shift the dates outside your date
range: 

PIVOT Format(
IIf([Preliminary Investigation Opened] <
CDate([Forms]![ReportForm]![Start_Date]),
DateAdd("d", -1, CDate([Forms]![ReportForm]![Start_Date])),
IIf([Preliminary Investigation Opened] >
CDate([Forms]![ReportForm]![End_Date]),
DateAdd("d", 1, CDate([Forms]![ReportForm]![End_Date])),
[Preliminary Investigation Opened])), "mmm")

/gustav


> i have a CrossTab query that gives result in  a following order

> Jan Feb Mar Apr Jun Jul etc...

> 4   55  2   12  43  6

> If the user selects as dates March 1 to April 31, i'll need to show the
> data combining everything before and after -- (the field names don't
> matter)

> Feb Mar Apr Jun

> 59  2   12  49

> is it possible to do in a crosstab query ?

> oleg


> TRANSFORM Count([Ron Data spr].[ID]) AS Total
> SELECT "Investigation Caseload" AS Status
> FROM [Ron Data spr]
> WHERE ([Ron Data spr].[Preliminary Investigation Opened] >=
> [Forms]![Report Form]![Start_Date]) And ([Ron Data spr].[Preliminary
> Investigation Opened]<=[Forms]![Report Form]![End_Date]) and [Preliminary
> Investigation Opened] is not null
> GROUP BY "Investigation Caseload"
> PIVOT Format([Preliminary Investigation Opened],"mmm") In
> ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");