working with date fields

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.

Answer 1

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...

Answer 2

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.

Answer 3

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