Not sure what to title it... (1 Viewer)

[Scottigus]

Good day Gents!

My problem is I have a database where I have recorded dates of when the employees last got certified for their forklift certifications (as well as others)...such as Person A - 8/11/2009, Person B - 9/2/2009, Person C - 7/2/2009, etc.

After a certain amount of time (let's say 6 months) they expire and the employee needs to be re-certified. So, on 2/11/2010, Person A needs to get re-certified, and then it would be entered as 2/11/2010 and 8/11/2009 would be erased. Okay...that's what it does.

The question I have is, I need to make a report/query that shows everyone from today's date that needs to get re-certified. Such as, today is 10/20/2009...if someone had their number at 4/21/2009, it would not show it, but tomorrow, when the report was ran, he would be on the list because it would have been 6 months since he last got certified. I am so hoping this makes sense...

So in essence, it would show everyone who is up for renewal. I checked online (googled) and all I kept finding was stuff for SQL or other programs.

Please, any help would be greatly appreciated

 

[pbaldy]

Something like

SELECT...
FROM...
WHERE DateAdd("m", 6, YourDateField) < Date()

 

[Anchoress]

I'm not a "Gent", but I assume it's OK if I respond?

There was a similar question posted recently, regarding upcoming birthdays: http://www.access-programmers.co.uk/forums/showthread.php?t=181136

And I had a somewhat similar question a few months ago where I wanted to generate a list of parent table records by overdue date, by comparing the current date to a) the most recent date of activity in the child table, and b) a number in the parent table that indicated the preferred frequency of contact: http://www.access-programmers.co.uk/forums/showthread.php?t=161419

I know they're not exactly like your question, but might still be useful.

 

[Scottigus]

seeing as I am still somewhat new to this...where exactly would I put all that information (SELECT, FROM, WHERE)

Thanks

 

[pbaldy]

Well, you posted this in Queries, so I posted query SQL. In a query in design view, paste this part in a new field:

DateAdd("m", 6, YourDateField)

Changing the field name of course, and this in the criteria row of that same field:

< Date()

 

[Scottigus]

Okay, I entered that into the appropriate place, but all it did was add 6 months onto the date.

What I'm trying to do is have the query only show the date that they got their certification only if it is later than 6 months from the day they got it.

Example: pbaldy got his certification on 04/28/2009, so if I ran the query today, it wouldn't run it because it is not 6 months from todays date, but if I ran it tomorrow (10/28/2009), it would now show up on the query because it is 6 months or more away from today's date...so todays date - 6 months... if they got their certificate less than 6 months ago, it wouldn't show it. If they got their certificate more than 6 months ago, it would now show up.

Any help would be greatly appreciated! Thanks!

 

[pbaldy]

Yes, that adds 6 months to the expiration date. When you add the criteria of

<Date()

to that field it should return those records that expired more than 6 months ago. Unless I'm having another brain cramp. Can you post a sample db?

 

[Scottigus]

Okay, I just redid the formula, but when making the query, I just added in the actual field to show the date that they received it on. I think I have it working now, so thank you veyr much!!!!

 

[pbaldy]

No problemo, glad we got it sorted out.