Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help with IIf function in query

    Hi all,

    I have a query and I would like to use the IIf function to find out if a date is in the following year (e.g. it's 2006, so I would like to see if the date is in the year 2007).

    Now the IIf function is being used on a date field in another table, that lists all of the events and dates for a particular school. Whenever I run the query it always picks the first date, which is before 2006.

    Is there anyway in a query to use the IIf function to check all of the event dates for that school not just the one?

    Hopefully that makes sense.

    Here is my query:

    Code:
    SELECT dbo_tblSchoolDetails.SchoolName, dbo_tblSchoolDetails.Town, dbo_tblSchoolDetails.County, Count(QUERCUS_PERSON.ID_NUMBER) AS CountOfID_NUMBER, IIf([EventDate]=Year([ACADEMIC_YEAR])+1,'Y','N') AS [Event Booked 06/07]
    FROM ((QUERCUS_STATUS INNER JOIN (((QUERCUS_PERSON INNER JOIN ((QUERCUS_STUDENT_COURSE_DETAIL INNER JOIN QUERCUS_COURSE_INSTANCE ON QUERCUS_STUDENT_COURSE_DETAIL.COURSE_INSTANCE = QUERCUS_COURSE_INSTANCE.OBJECT_ID)
    INNER JOIN QUERCUS_MODE_OF_STUDY ON QUERCUS_COURSE_INSTANCE.MODE_OF_STUDY = QUERCUS_MODE_OF_STUDY.OBJECT_ID) ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.PERSON)
    INNER JOIN (QUERCUS_SCHOOL_ATTENDED INNER JOIN QUERCUS_SCHOOL ON QUERCUS_SCHOOL_ATTENDED.SCHOOL = QUERCUS_SCHOOL.OBJECT_ID) ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_SCHOOL_ATTENDED.PERSON)
    INNER JOIN tblQuercusLearnJoined ON QUERCUS_PERSON.OBJECT_ID = tblQuercusLearnJoined.PERSON) ON QUERCUS_STATUS.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.STATUS)
    INNER JOIN dbo_tblSchoolDetails ON QUERCUS_SCHOOL.SCHOOL = dbo_tblSchoolDetails.SchoolName) LEFT JOIN dbo_tblSchoolEventDetails ON dbo_tblSchoolDetails.ID = dbo_tblSchoolEventDetails.SchoolID
    WHERE (((QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR)=[Forms]![frmReports]![txtStartYear]) AND ((QUERCUS_MODE_OF_STUDY.MODE_OF_STUDY) In ('FULL-TIME_FULL_Y','SANDWICH','FULL-TIME_LESS_T','FTS','04'))
    AND ((QUERCUS_COURSE_INSTANCE.COURSE_YEAR)=1) AND ((QUERCUS_STATUS.STATUS) In ('R','TRANSFER')))
    GROUP BY dbo_tblSchoolDetails.SchoolName, dbo_tblSchoolDetails.Town, dbo_tblSchoolDetails.County, IIf([EventDate]=Year([ACADEMIC_YEAR])+1,'Y','N'), QUERCUS_SCHOOL.SCHOOL
    HAVING (((QUERCUS_SCHOOL.SCHOOL) Not In ('REASEHEATH COLLEGE') And (QUERCUS_SCHOOL.SCHOOL) Is Not Null))
    ORDER BY QUERCUS_SCHOOL.SCHOOL
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would create a calculated field in your query using the Year function, then filter for the given year. In your Query design add :

    EventYear:Year([EventDate])

    And then add 2006 in the criteria.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •