Solved

Access 2007 query to count entries within specified date range

Posted on 2011-09-29
5
326 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

786 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