• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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
  • 2
  • 2
1 Solution
Arno KosterCommented:
in general you should be looking for something like

SELECT [...] FROM [proposal data] WHERE date LIKE '%2011%' GROUP BY [...] HAVING [...]
Arno KosterCommented:
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...
barnescsAuthor Commented:
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 MatthewsCommented:
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...
barnescsAuthor Commented:
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!!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now