none
working with date fields RRS feed

  • Question

  • the question:
    someone is a member of an association. I want an overview of all jubilees in a year.
    Which means:
    Who will be 12.5 years in 2019 and 25 years member and 40 years member
    The date member since is in the table but how do I get the information out.
    Tuesday, February 19, 2019 8:59 PM

All replies

  • Hi. The DateDiff() function can tell you what anniversary will be happening for each member for this year. For example:

    DateDiff("yyyy",[MembershipDate],Date())

    Hope it helps...

    Tuesday, February 19, 2019 9:07 PM
  • someone is a member of an association. I want an overview of all jubilees in a year.

    Hi Sjaak,

    A different approach.

    In one of my applications for an organization where jubilees are very important, I collected all the jubilees in a Jubilee_tbl. When a new member is entered, his jubilees are added to the table, and again removed after leaving the organization. The fields in this table are: Jubilee-date, Kind of Jubilee, Member_id.

    It is a redundant table, but very handy to make all kind of year-overviews, without making complicated queries.

    Imb.

    Tuesday, February 19, 2019 10:13 PM
  • For the 12.5 jubilee you'd need to work in months, so a query to return all members with 12.5, 25 or 40 years membership in 2019 would be along these lines:

    SELECT *
    FROM Membership
    WHERE Year(DateAdd("m",150,[MemberSince])) =2019
          OR Year(DateAdd("yyyy",25,[MemberSince])) = 2019
          OR Year(DateAdd("yyyy",40,[MemberSince])) = 2019;

    Ken Sheridan, Stafford, England

    Tuesday, February 19, 2019 11:31 PM