[AccessD] another CrossTabulation question

Hi Oleg

Try removing the CDate(..)s.
Then add to the query these parameters of date type Date/Time:

[Forms]![Report Form]![Start_Date]
[Forms]![Report Form]![End_Date]

If you wish to include any date outside the interval, just remove that
from the WHERE clause:

WHERE [Preliminary Investigation Opened] Is Not Null

/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");