[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2007 query to count entries within specified date range

Posted on 2011-09-29
5
Medium Priority
?
349 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 2000 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 93

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

649 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