Use Specific date range when extracting data 2

[Richo1980]

Hi,

I have a query that is extracting information from an Access DB and displaying it in a table on a webpage

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "select [TSK Assigned To Individual], sum(iif([TSK Assigned To Group] ='GMS-Admin', 1, 0)) as GMSAdmin from TSK group by [TSK Assigned To Individual] ORDER BY Sum(IIf([TSK Assigned To Group]='GMS-Admin',1,0)) DESC", conn

This works as expected and I am given an officers name plus a total of how many calls they have closed (in Desc order). What I would like to do is only show records after the 1/1/2007.. Can someone please advise how I would go about incorporating this into the existing query?

Thanks

 

[SkipVought]

Hi,

StartDate = #1/1/2007#
EndDate = #12/31/2007#

sSQL = "select [TSK Assigned To Individual], sum(iif([TSK Assigned To Group] ='GMS-Admin', 1, 0)) as GMSAdmin from TSK [b]Where [TheDateField] Between " & StartDate & " and " & EndDate & "[/b] group by [TSK Assigned To Individual] ORDER BY Sum(IIf([TSK Assigned To Group]='GMS-Admin',1,0)) DESC"

rs.Open sSQL, conn

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

Like this ?
rs.Open "SELECT [TSK Assigned To Individual], Sum(IIf([TSK Assigned To Group]='GMS-Admin', 1, 0)) AS GMSAdmin FROM TSK WHERE [your date field]>=#2007-01-01# GROUP BY [TSK Assigned To Individual] ORDER BY 2 DESC", conn

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Richo1980]

thanks for the advise...works perfectly

 

[Richo1980]

Oh just one more thing...

If I want to select a specific daterange how do I do that? Say I only want to show dates between 1/1/2006 and 31/12/2006?

Thanks again

 

[PHV]

Reread carefully Skip's suggestion.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886