Solved

Access 2007 query to count entries within specified date range

Posted on 2011-09-29
5
344 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:barnescs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
Arno Koster earned 500 total points
ID: 36814072
in general you should be looking for something like

SELECT [...] FROM [proposal data] WHERE date LIKE '%2011%' GROUP BY [...] HAVING [...]
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36814238
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...
0
 

Author Comment

by:barnescs
ID: 36814352
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%"
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36814416
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...
0
 

Author Comment

by:barnescs
ID: 36814484
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!!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question