Prompts and/or reminders?

[BetaMike]

We record each employee’s DateOfBirth in tblEmployees and want to create an
alert when they reach retirement age (65yrs in the UK). Employees can choose
to work beyond their 65th birthday if they wish so we need some kind of
prompt to remind us to contact them in advance.

I have entered the following expressions in the relevant qryEmployees

DateOfRetirement: DateAdd("m",780,[DOB])
RequestMedical: [DateOfRetirement]-90

This shows the date of retirement and displays a date 90 days prior to this.
Would it be possible to setup some kind of alert when a user logs in? Any
suggestions would be appreciated

Thanks.

 

[Jeff Boyce]

So, your query returns DateOfRetirement and RequestMedical when it runs.

Could you create another query, based on the first one, that only returns
the rows for which those two are, say, in the next week?

If you want something to run at startup, you can use a macro -- if you name
it Autoexec, it runs at startup. Or you could create a procedure tied to
the first form that opens...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

You can have your query results as a condition in the macro Autoexec.

 

[BetaMike]

Hmm, I can return the relevant test records if I use a 'Between #?# And #?#'
criteria but that would mean changing the query every 3 months or so.

I was hoping to compare todays date with DateOfRetirement and return each
record where the difference is less than <=90 days. These records could then
feed a report which could run using the Autoexec macro?

Apologies if I read your suggestion wrongly but this is a very steep
learning curve for me

Thanks.

So, your query returns DateOfRetirement and RequestMedical when it runs.

Could you create another query, based on the first one, that only returns
the rows for which those two are, say, in the next week?

If you want something to run at startup, you can use a macro -- if you name
it Autoexec, it runs at startup. Or you could create a procedure tied to
the first form that opens...

Regards

Jeff Boyce
Microsoft Office/Access MVP

We record each employee's DateOfBirth in tblEmployees and want to create
an
alert when they reach retirement age (65yrs in the UK). Employees can
choose
to work beyond their 65th birthday if they wish so we need some kind of
prompt to remind us to contact them in advance.

I have entered the following expressions in the relevant qryEmployees

DateOfRetirement: DateAdd("m",780,[DOB])
RequestMedical: [DateOfRetirement]-90

This shows the date of retirement and displays a date 90 days prior to
this.
Would it be possible to setup some kind of alert when a user logs in? Any
suggestions would be appreciated

Thanks.

 

[BetaMike]

Wow! Reading these boards must be having a positive effect on me

I don't need the RequestMedical field as I used
RequestMedical: DateDiff("d",Date(),[qryEmployees]![DateOfRetirement]) with
a criteria of

0 And <=90

This returns the relevant DateOfRetitrement records that are less than 90
days away. I included the ">0" to prevent the listing of minus figures
(already over the age of retirement.

Now I need to write a macro that opens up a Report based on the above query
:-/

Thanks.

So, your query returns DateOfRetirement and RequestMedical when it runs.

Could you create another query, based on the first one, that only returns
the rows for which those two are, say, in the next week?

If you want something to run at startup, you can use a macro -- if you name
it Autoexec, it runs at startup. Or you could create a procedure tied to
the first form that opens...

Regards

Jeff Boyce
Microsoft Office/Access MVP

We record each employee's DateOfBirth in tblEmployees and want to create
an
alert when they reach retirement age (65yrs in the UK). Employees can
choose
to work beyond their 65th birthday if they wish so we need some kind of
prompt to remind us to contact them in advance.

I have entered the following expressions in the relevant qryEmployees

DateOfRetirement: DateAdd("m",780,[DOB])
RequestMedical: [DateOfRetirement]-90

This shows the date of retirement and displays a date 90 days prior to
this.
Would it be possible to setup some kind of alert when a user logs in? Any
suggestions would be appreciated

Thanks.