How do I create a Flag to let me know when 30 days from completion

[ArtistRhetta]

I have been working on an Access database, now they want to know if there is a way to Flag them when employees have reached 30 days since completion of their training. I have the fields, completion date, employees and training type. Any ideas anyone? Would a query work?

 

[willir]

ArtistRhetta

I trust your database design includes either a start date which allows you to calculate the expiration date, or your design inlcudes an expiration date.

Several ticks can be used...

Run a report where the difference today's date and the expiration date is 30 or less days.

When an end user brings up a form, the expiration date could use a background colour of red or yellow for the field.

If your schema includes an email address, and you feel ambitous, you can send them an email via Access.

As you can see, a lot of these approaches depend on your schema.

Richard

 

[scriverb]

Create a new query with the following SQL and name it qryDaysSinceTraining:

Select A.[Employee_Name], A.[Training_Type], A.[Completion_Date], DateDiff("d",A.[Completion_Date],Date()) as Days_Since_CompletionOfTraining 
FROM [red][i]yourtablename[/i][/red] as A 
WHERE DateDiff("d",A.[Completion_Date],Date())>=28 and DateDiff("d",A.[Completion_Date],Date())<=32 ;

Create a report using the above query as the RecordSource.

Now put this code in the OnOpen of the first form that opens in your database.

If DCount("*", "qryDaysSinceTraining") > 0 then
   DoCmd.OpenReport "[red][i]yourreportname[/i][/red]", acViewPreview
End If

The query will select employees whos days since completion range from 28 to 32. This allows for a little wiggle room so if you don't run the program everyday or it is over a long weekend when the 30 days will calculate the employee will show up on the report. This can be adjusted by you if you wish.

Post back if you have further questions.



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[BobJacksonNCI]

Bob,

Your signature is nice!

Wonder where I can find a Hawkeye...

Bob

 

[cranebill]

Would be a better signature if it were Michigan and not State

Bill

 

[scriverb]

Bob: Just go to the Iowa website and search around. They usually have a list of available pics for downloading. Or, just right click and copy any pic that you see that you like.

Bill: Mi#$@&%$ who?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[cranebill]

lol
It's all good... either way we are Michigan professionals lol

Bill

 

[willir]

Bob - Lessons from the AceMan?? Cool. So is your solution.

 

[scriverb]

willir: Thanks for that. Had to think of simple way to list them for a few days and then have them fall off without having to flag them or something. Thus the day range. Yea, the AceMan led the way.

Bill: You bet. Did you see the last shot last night for Sacramento. He choked again. Must be all that cash that he didn't take weighted him down. LOL ;-)

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[cranebill]

Yes I saw that... but I am more concerned about tonight's game lol.. I mean for goodness sakes it is getting so hyped, local radio stations are having "The World's Biggest Fro" Contest lol.

I AM BEN WALLACE lol

Bill

 

[scriverb]

Then you should be updating your signature with the following:



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[cranebill]

lol... since I see you have a Michigan Forum We should probably take this there... and Larry.... BENCH Prince... he isn't shooting anyway lol

Bill

 

[scriverb]

Yea, that fine I will see you in the Michigan forum.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ArtistRhetta]

Thanks for the great information. I will work with it this week and see how it goes. I am in the learning mode and once I created a few Access databases at work now I am being stormed and each one has new challenges. Enjoyed the responses as well. Nice logo.

 

[willir]

ArtistRhetta

We have all been there. Good luck in your new journey...

 

[BobJacksonNCI]

Bob,

I found a small Hawkeye jpeg, but don't see how to add it to my profile.

How did/do you include your spartan?

HTH,
Bob

 

[RoyVidar]

Include an image:
[blue]

[b][ignore][img]http://UrlToTheLocationOfPic[/img][/ignore][/b]

[/blue]

Check out the Process TGML link at the bottom of the reply pane. Hope I'm not offending by "stealing" the picture;-)

Roy-Vidar

 

[scriverb]

RoyVidar: No offense at all. The more I see it the better.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]