Query based on the value of a calculated field

[Guest]

Hi,

I'm trying to display records based on the value of a calculated field.
Basically Our staff attend training courses and then they have a compliancy
period before they need to update that same training.

Now we do not store the expiry date we calculate this when needed. What
we're trying to do now is produce a query that will allow us to forecast
training needs. In order to do this obviously we will need to display
records based on the value of this expiry field.

I've tried several different options and have drawn a blank. Can somebody
please help??

Ian

 

[Jeff Boyce]

Ian

Are you saying you have a record of who, which course, and on which date,
and wish to tell when you've exceeded, say, 3 months after that date?

A query that returned who, which course, on which date would do this, if you
added a selection criterion that compared the "on which date" to today's
date, less 3 months (or whatever compliancy period you wish). Take a look
at using the DateAdd() function in the selection criterion.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

We have a table of courses each with their own compliancy periods measured in
months. The periods are generally 12, 24 and 36, some however are not
compliancy based. We have a seperate table called training which stores the
details of attended dates etc.

The calculation we currently use to view expiry dates is:

Expiry: CVDate(IIf(IsNull([Compliancy Period]), Null, DateAdd("m",
[Compliancy Period], [Date Completed])))

 

[Jeff Boyce]

Ian

It seems like you'd be able to join the person's record of a course with
that course's compliancy period in your query.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

We have a table of courses each with their own compliancy periods measured in
months. The periods are generally 12, 24 and 36, some however are not
compliancy based. We have a seperate table called training which stores the
details of attended dates etc.

The calculation we currently use to view expiry dates is:

Expiry: CVDate(IIf(IsNull([Compliancy Period]), Null, DateAdd("m",
[Compliancy Period], [Date Completed])))

Ian

Are you saying you have a record of who, which course, and on which date,
and wish to tell when you've exceeded, say, 3 months after that date?

A query that returned who, which course, on which date would do this, if you
added a selection criterion that compared the "on which date" to today's
date, less 3 months (or whatever compliancy period you wish). Take a look
at using the DateAdd() function in the selection criterion.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/