asked on

Access 2007 query to count entries within specified date range

i have this Access query:
SELECT Count([proposal data].[CPP #]) AS [CountOfCPP #], Sum(IIf([DATE CONTRACT AWARD] Is Null,0,1)) AS NoOfContractAwarded, Sum([proposal data].[TOTAL HOURS BID]) AS [SumOfTOTAL HOURS BID], Sum([proposal data].[TOTAL TAD HOURS]) AS [SumOfTOTAL TAD HOURS], [proposal data].PROGRAM INTO [TOTAL SPARES]
FROM [proposal data]
GROUP BY [proposal data].CATEGORY, [proposal data].PROGRAM
HAVING ((([proposal data].CATEGORY)="SPARES"));

in this table i have a completed date which is the date the bid was completed. I want to add a criteria to my query that will pull only the information with a 2011 date. I have tried adding this to my query but it lists each date. I don't need the information totaled by date, i just need the information that has a date greater than 12/31/2010

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arno Koster

PS> it might be a good idea to add the MS Access zone to this question, I guess you will get a lot more response from this zone...

barnescs

ASKER

This is what that statement looks like now and i get zero rows of data with it:

FROM [proposal data] where [DATE COMPLETED] LIKE "%2011%"

Patrick Matthews

For a specific date, such as 31 Dec 2011:

WHERE [DATE COMPLETED] >= #31 Dec 2011# And [DATE COMPLETED] < #1 Jan 2012#

Open in new window

I prefer that to simply "WHERE [DATE COMPLETED] = #31 Dec 2011#" because the above construction works whether the date has a time component or not.

I am assuming, of course, that you are using the Date data type for your [DATE COMPLETED] column...

barnescs

ASKER

yes i am using the Date data type.
i played with the code until i got this to work for me:
FROM [proposal data] where [DATE COMPLETED] LIKE "*2011*"

Thanks for your help!!!!