Is this possible...?

[YNWA]

Hi, I have a query pulling through Staff details and Training details.

In my training details I have the fields...

trainID
date
time
coursecode
coursename
venue
trainer

Training for specific course happen every 1 or 2 years.

Is it possible to calculate how many dates from their last date for a specific course they have left until they need to redo the training?

Say I need Fire training every 12 month and CPR every 24 months.

Can I create a query for Fire that when run pops a pop up asking for course name (I can do this with [Course Name] in the coursename field) but then also show the previous Fire training and a field of how many days until the next Fire training must be booked??

I am thinking of maybe having a field called DueDate which self populates exactly 12 or 24 months ahead, thus giving us in theory an END DATE.

Then run a field called DateDiff, comparing the date field against our DueDate field giving us a number of days/months until next course is booked.

Have I just answered my own question there?

Thanks
will

 

[DCrake]

If you know the time span between events you can use the DateAdd() to calculate the next due date, for example NextFire= DateAdd("y",2,LastFire).

Now you can do a DateDiff() between the Today and NextFire

DueInDays = DateDiff("d",DateAdd("y",2,LastFire),Date)

This wil show you how many days between today and when their next course is due

You could actually put them both together

David

 

[YNWA]

If you know the time span between events you can use the DateAdd() to calculate the next due date, for example NextFire= DateAdd("y",2,LastFire).

Now you can do a DateDiff() between the Today and NextFire

DueInDays = DateDiff("d",DateAdd("y",2,LastFire),Date)

This wil show you how many days between today and when their next course is due

You could actually put them both together

David

Cheers mate.

I have put the following in....

NextTraining: DateAdd("m",12,[fldDate]) - This gives me a date for the next training session.

Next field is...

DueInMonth: DateDiff("m",DateAdd("m",12,[fldDate]),Date) - This pops up a parameter box for me to enter a date. THus giving me a total in months until next session is due.

All look ok to you? Is it possible to get the query to run on todays date without having to imput it ? So it runs automatically when clicked?

 

[DCrake]

You need to put () adter the date to tel Access to use tody's Date

David

 

[YNWA]

Cheers. Would you say its best to link the subform to the query via record source by putting in qryStaff_TrainingDates.NextTraining into the Record source?

Is it bad practice to put more than one table/query into the record source? As I currently have subform linking to tblTraining.

Or can I simply put another field on the subform and put some code in there to pull through the training due date from the qry?

 

[Suncoast]

very easy way of doing this
in your query do an expr1: now() - [table1]![date]
then in your criteria do a >365 for 1 year
this will give you just the records that have a date over 1 year. you can modify the number accordinly.

 

[YNWA]

very easy way of doing this
in your query do an expr1: now() - [table1]![date]
then in your criteria do a >365 for 1 year
this will give you just the records that have a date over 1 year. you can modify the number accordinly.

Cheers mate, I am using this in a seperate query to bring back people who have not had training over 365 days ago.

The result however pulls through as say 400.343875 days, is it possible to refine this down to zero decimal places?

Also is it possible to show the result in how many days late it is, instead of 400 days since last training, show it as 35 days late, as its 35 days overdue?

Thanks
Will

 

[Suncoast]

fix(now() - [table1]![date]) will truncate the decimal (int will round)

therefore:

dayslate: fix(now() - [table1]![date]) - 365

criteria: >0

 

[YNWA]

Cheers fella!

 

[Galaxiom]

fix(now() - [table1]![date]) will truncate the decimal (int will round)

That isn't correct.

For positive numbers Int() and Fix() return identical results.
For negative numbers Fix() returns the integer section of the number while Int() returns the next integer more negative than the number.
http://office.microsoft.com/en-us/access/HA012288591033.aspx

But there is no need to use either of them in this case.

Instead of using Now() which returns the date and time use Date() which just returns the date.
And for the sake of completeness I'll mention Time() returns just the time.