[AccessD] another CrossTabulation question

Hi Gustav

I am getting this erroe -- The Microsoft Jet database engine does not
recognise '[Forms]![Report Form]![Start_Date]' as a valid field or
expression...

It works fine with original pivot, and [Forms]![Report Form]![Start_Date]
is written everywhere in the same way..

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(
IIf([Preliminary Investigation Opened] < CDate([Forms]![Report
Form]![Start_Date]), DateAdd("d", -1, CDate([Forms]![Report
Form]![Start_Date])),
IIf([Preliminary Investigation Opened] > CDate([Forms]![Report
Form]![End_Date]), DateAdd("d", 1, CDate([Forms]![Report
Form]![End_Date])),
[Preliminary Investigation Opened])), "mmm")

p.s. doesn't nmatter about calendar year

> 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");
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



-----------------------------------------
Get Breaking News from CNN, ABC, NBC, CBS Now.
http://www.xuppa.com/news/?link=webmail