eossma
asked on
Access 2007: Year(Date) Expression where Date has Start & End Point parameters
I have a query with a Year(Date) Expression, where Date has Start & End Point BETWEEN parameter. Works beautifully.
If I enter my start date as 01/01/14, and end date as 07/31/15, how can I get my Year(Date) Expression field to return 2015 instead of 2014?
The query:
__________________________ __________ __________ __________ __________ __________ ____
PARAMETERS [Start Date (Example: 01/01/15)] DateTime, [End Date (Example: 01/30/15)] DateTime;
SELECT DISTINCT Course.AddressID, Course.CourseDate, Course.CourseName, Course.CCOClass, Course.Location, Year([Course.CourseDate]) AS [Year], Month([Course.CourseDate]) AS MonthNo, GetNumberOfMonth([MonthNo] ) AS NumberOfMonth, qryCourseDate2.EmpNo
FROM (Course INNER JOIN Students ON Course.AddressID = Students.AddressID) INNER JOIN qryCourseDate2 ON Course.AddressID = qryCourseDate2.AddressID
GROUP BY Course.AddressID, Course.CourseDate, Course.CourseName, Course.CCOClass, Course.Location, qryCourseDate2.EmpNo
HAVING (((Course.CourseDate) Between [start date (Example: 01/01/15)] And [end date (Example: 01/30/15)]))
ORDER BY Course.CourseDate;
If I enter my start date as 01/01/14, and end date as 07/31/15, how can I get my Year(Date) Expression field to return 2015 instead of 2014?
The query:
__________________________
PARAMETERS [Start Date (Example: 01/01/15)] DateTime, [End Date (Example: 01/30/15)] DateTime;
SELECT DISTINCT Course.AddressID, Course.CourseDate, Course.CourseName, Course.CCOClass, Course.Location, Year([Course.CourseDate]) AS [Year], Month([Course.CourseDate])
FROM (Course INNER JOIN Students ON Course.AddressID = Students.AddressID) INNER JOIN qryCourseDate2 ON Course.AddressID = qryCourseDate2.AddressID
GROUP BY Course.AddressID, Course.CourseDate, Course.CourseName, Course.CCOClass, Course.Location, qryCourseDate2.EmpNo
HAVING (((Course.CourseDate) Between [start date (Example: 01/01/15)] And [end date (Example: 01/30/15)]))
ORDER BY Course.CourseDate;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
MAX(Year([Course.CourseDat