John - Thanks so much for helping me with this. Below is the SQL statement without the criteria of showing me the last years worth of data. My ultimate goal is to have my report always show me the past twelve months worth of data.
SELECT Format([Date Arrived in PCC],"mm/yyyy") AS [Date Arrived], Avg(Format(WorkDays([Date Arrived in PCC],[ECO Routing Complete]),"#,###.0")) AS [Work Days], Count(tblReleases.[ECN Number]) AS [Number of ECN's] FROM tblReleases GROUP BY Format([Date Arrived in PCC],"mm/yyyy"), tblReleases.[ECN Class] HAVING (((tblReleases.[ECN Class])=[Forms]![frmECNPriority]![ECN Class])) ORDER BY Format([Date Arrived in PCC],"mm/yyyy"); Dawn Crosier Application Specialist "Education Lasts a Lifetime" -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of John Viescas Sent: Thursday, January 05, 2006 1:35 PM To: [email protected] Subject: RE: [ms_access] Date Criteria Dawn- That criteria makes no sense. If it's truly a date/time field, how you format it should have no bearing - unless you're using the Format function. If you are using the Format function, that's returning a string, which would be very hard to use in a comparison. Basically, Month(Date()) should currently return the integer value 1. Subtract 14, and you get -13. Only a date before December 17, 1899 should be less than or equal to that value. What's the SQL of your query? Is it really a date/time data type? John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Crosier, Dawn Sent: Thursday, January 05, 2006 1:23 PM To: [email protected] Subject: [ms_access] Date Criteria I am having a block. I have a field in my query which is a date field. I have formatted the field so that it returns mm/yyyy. I want to setup the criteria of the field so that it always shows me only the last 12 months. I have <=Month(Date())-14 as the criteria now, and that does show me the January through December 2005 records, but I don't understand why I have to have a -14? It seems more logical to have a -13 so that I can use last month as the ending range. Dawn Crosier Application Specialist "Education Lasts a Lifetime" Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
