Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    querying a calculated field (2000)

    Hi.

    I have created a query that creates a calculated field (n years added to a date field). The query is also a group by query as I only want the last courses (max date) of all the courses that I am querying. When I try and put a where clause on the calculated field, I get a "Data Type mismatch in criteria expression" error message. I have formatted the calculated field into being a short date format and that does not seem to solve the issue. The sql is:

    SELECT [Courses-Regard course names].Course, Max([Courses-Regard].Date) AS CourseDate, [Courses-Regard].Duration, DateAdd("yyyy",[Duration],[Date]) AS [Renewal Date], Staff.Surname, Staff.FirstName, Staff.Home
    FROM (([Courses-Regard] INNER JOIN [Courses-Regard course names] ON [Courses-Regard].CNameID = [Courses-Regard course names].CNameID) INNER JOIN [HQ Course attendance] ON [Courses-Regard].CourseID = [HQ Course attendance].CourseID) INNER JOIN Staff ON [HQ Course attendance].StaffID = Staff.StaffID
    GROUP BY [Courses-Regard course names].Course, Staff.StaffID, Staff.[Left?], [Courses-Regard].Duration, DateAdd("yyyy",[Duration],[Date]), Staff.Surname, Staff.FirstName, Staff.Home
    HAVING (((Staff.[Left?])=True) AND (Not ([Courses-Regard].Duration) Is Null));

    As always, any help much appreciated

    Amanda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: querying a calculated field (2000)

    Shouldn't Renewal Date have Max as Total option instead of Group By? And do you want to group by Duration?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querying a calculated field (2000)

    Thanks Hans

    As usual you are right, on both counts. It now works fine.

Posting Permissions

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