Link to home
Start Free TrialLog in
Avatar of eossma
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;
Avatar of eossma
eossma

ASKER

I've tried Max like this:

MAX(Year([Course.CourseDate])), but it doesn't work.
ASKER CERTIFIED SOLUTION
Avatar of eossma
eossma

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial