[AccessD] another Transform query question

thanks a lot, seems to be working :--)
now I'll do another one taking away the missing months llike Brett
suggested, and see which the like better

> Oleg,
>
> I don't think you really need the IIF.  The where statement using just
> the dates should be enough and might speed up the query.
>
>
>
>> TRANSFORM Count([Ron Data Mid OP].[ID]) AS Total
>> SELECT "Investigation Opened" AS Status
>> FROM [Ron Data Mid OP]
>
>> WHERE ([Ron Data Mid OP].[MS Date Opened] >= [Forms]![Report
>> Form]![Start_Date]) And ([Ron Data Mid OP].[MS Date
>> Opened]<=[Forms]![Report Form]![End_Date])
>>
>>
>> GROUP BY "Investigation Opened"
>>
>>
>> PIVOT Format([AMTS Date Opened],"mmm") In
>> ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","
>> Nov","Dec");
>>
>
> Patti
>
>> -----Original Message-----
>> From: O'Connor, Patricia [mailto:Patricia.O'Connor at dfa.state.ny.us]
>> Sent: Monday, February 23, 2004 01:51 PM
>> To: 'Access Developers discussion and problem solving'
>> Subject: RE: [AccessD] another Transform query question
>>
>>
>>
>> Try
>>
>> TRANSFORM Count([Ron Data Mid OP].ID) AS Total
>> SELECT "Investigation Opened" AS Status
>> FROM [Ron Data Mid OP]
>> WHERE (((IIf([Ron Data Mid OP]![MS Date Opened]>=[Forms]![Report
>> Form]![Start_Date] And [Ron Data Mid OP]![MS Date
>> Opened]<=[Forms]![Report
>> Form]![End_Date],"Cases Closed")) Is Not Null))
>>
>>
>> GROUP BY "Investigation Opened"
>>
>>
>> PIVOT Format([AMTS Date Opened],"mmm") In
>> ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","
>> Nov","Dec");
>>
>>
>> Patti
>>
>> > -----Original Message-----
>> > From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com]
>> > Sent: Monday, February 23, 2004 12:59 PM
>> > To: accessd at databaseadvisors.com
>> > Subject: RE: [AccessD] another Transform query question
>> >
>> >
>> > I would hope it would be that simple.. nee, it keeps
>> prompting me for
>> > status :((
>> > (not to mention that it takes like 4 minutes for a query to open)
>> Also, they change their mind, they now want it to prompt,
>> > like I had in
>> > the first one..
>> >
>> > TRANSFORM Count([Ron Data Mid OP].ID) AS Total
>> > SELECT "Investigation Opened" AS Status
>> > FROM [Ron Data Mid OP]
>> > WHERE (((IIf([Ron Data Mid OP]![MS Date Opened]>=[Forms]![Report
>> Form]![Start_Date] And [Ron Data Mid OP]![MS Date
>> > Opened]<=[Forms]![Report
>> > Form]![End_Date],"Cases Closed")) Is Not Null))
>> > GROUP BY [Status]
>> > PIVOT Format([AMTS Date Opened],"mmm") In
>> > ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","
>> Nov","Dec");
>> >
>> >
>> > whats the better way to do prompts ?
>> >
>>
>>
>> >
>> _______________________________________________
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
> _______________________________________________
> 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