Solved

Access 2007 query to count entries within specified date range

Posted on 2011-09-29
5
308 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
  • 2
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
akoster 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:akoster
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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 …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now