Date calculation

[Guest]

I'm building a db to track employee hire dates, and their eligibility for
company awards, which are at this time simply 5 Year, 10 Year, etc. longevity
awards. The user has requested that the form show the next award the current
employee is eligible for, and on what date. (This is just extra info for the
user while she goes about other tasks. The intent is to design a report that
will show all employees eligible for any award in the coming 12 months.)

So, without writing it for me, can you direct me to a similar example?
Obviously I'm going to have to divide the difference between the current date
and the hire date by 5 and test it for wholeness. What function will I be
using? What form event will I tie the code to?

Thanks

 

[Allen Browne]

Use DateDiff() to calculate the difference in years between the today and
the HireDate. Subtract 1 if the anniversary of the hire date has not occured
yet this year. Use Mod to get the remainder after dividing by 5. If the
result is zero, select the record.

You will end up with something like this in the WHERE clause of your query:

WHERE (((DateDiff("yyyy", [HireDate], Date()) +
(DateSerial(Year(Date()), Month([HireDate]), Day([HireDate])) > Date()))
Mod 5) = 0)


It might be easier, more efficient, and more flexible to use a Cartesian
product to get the anniversary of the dates. This involves creating a table
named (say) tblCount, with a Number field named CountID. Enter the records:
5
10
15
20
25
...

Then create a query that uses the Employee table and tblCount. Make sure
there is no line joining the 2 queries in the upper pane of the query design
window. In a fresh column in the field row, enter:
Anniversary: DateAdd("yyyy", [CountID], [HireDate])
In the Criteria row under this field, you can enter the range of dates you
are interested in seeing. e.g. for this month:
Between #1/1/2006# And #1/31/2006#

 

[Guest]

Hmm, I'm trying your second idea, and the query wizard is telling me the two
tables, tblEmployees and tblCount need to be related. One to many, an
employee gets a "count" everytime he/she gets an award?

Use DateDiff() to calculate the difference in years between the today and
the HireDate. Subtract 1 if the anniversary of the hire date has not occured
yet this year. Use Mod to get the remainder after dividing by 5. If the
result is zero, select the record.

You will end up with something like this in the WHERE clause of your query:

WHERE (((DateDiff("yyyy", [HireDate], Date()) +
(DateSerial(Year(Date()), Month([HireDate]), Day([HireDate])) > Date()))
Mod 5) = 0)


It might be easier, more efficient, and more flexible to use a Cartesian
product to get the anniversary of the dates. This involves creating a table
named (say) tblCount, with a Number field named CountID. Enter the records:
5
10
15
20
25
...

Then create a query that uses the Employee table and tblCount. Make sure
there is no line joining the 2 queries in the upper pane of the query design
window. In a fresh column in the field row, enter:
Anniversary: DateAdd("yyyy", [CountID], [HireDate])
In the Criteria row under this field, you can enter the range of dates you
are interested in seeing. e.g. for this month:
Between #1/1/2006# And #1/31/2006#

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm building a db to track employee hire dates, and their eligibility for
company awards, which are at this time simply 5 Year, 10 Year, etc.
longevity
awards. The user has requested that the form show the next award the
current
employee is eligible for, and on what date. (This is just extra info for
the
user while she goes about other tasks. The intent is to design a report
that
will show all employees eligible for any award in the coming 12 months.)

So, without writing it for me, can you direct me to a similar example?
Obviously I'm going to have to divide the difference between the current
date
and the hire date by 5 and test it for wholeness. What function will I be
using? What form event will I tie the code to?

Thanks

 

[Allen Browne]

No. It is important that there is no relation between the 2 tables. That way
the query returns every possible combination, i.e. every anniversary for
every employee.

Try creating a query in design view, rather than following any of the other
wizards.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hmm, I'm trying your second idea, and the query wizard is telling me the
two
tables, tblEmployees and tblCount need to be related. One to many, an
employee gets a "count" everytime he/she gets an award?

Use DateDiff() to calculate the difference in years between the today and
the HireDate. Subtract 1 if the anniversary of the hire date has not
occured
yet this year. Use Mod to get the remainder after dividing by 5. If the
result is zero, select the record.

You will end up with something like this in the WHERE clause of your
query:

WHERE (((DateDiff("yyyy", [HireDate], Date()) +
(DateSerial(Year(Date()), Month([HireDate]), Day([HireDate])) > Date()))
Mod 5) = 0)


It might be easier, more efficient, and more flexible to use a Cartesian
product to get the anniversary of the dates. This involves creating a
table
named (say) tblCount, with a Number field named CountID. Enter the
records:
5
10
15
20
25
...

Then create a query that uses the Employee table and tblCount. Make sure
there is no line joining the 2 queries in the upper pane of the query
design
window. In a fresh column in the field row, enter:
Anniversary: DateAdd("yyyy", [CountID], [HireDate])
In the Criteria row under this field, you can enter the range of dates
you
are interested in seeing. e.g. for this month:
Between #1/1/2006# And #1/31/2006#

I'm building a db to track employee hire dates, and their eligibility
for
company awards, which are at this time simply 5 Year, 10 Year, etc.
longevity
awards. The user has requested that the form show the next award the
current
employee is eligible for, and on what date. (This is just extra info
for
the
user while she goes about other tasks. The intent is to design a
report
that
will show all employees eligible for any award in the coming 12
months.)

So, without writing it for me, can you direct me to a similar example?
Obviously I'm going to have to divide the difference between the
current
date
and the hire date by 5 and test it for wholeness. What function will I
be
using? What form event will I tie the code to?

 

[Kevin Justesen]

Loll