Annual Testing Date Problems

[wreded]

i've tried searching on this, but i can't seem to find anything close enough.

Table contains IndvName, IATestDate, OfcSym. Individuals must undergo annual training to remain proficient. i want to track this on a monthly basis. Current i can get Year trained and month trained, but can't find individuals that are more than 1 month overdue.

What i want to do is catch everyone that is one month or more overdue on their training and get it all caught up, then get us on a monthly schedule to make sure we stay that way.

Currently:

SELECT Users.IndvName, Users.OfcSym, Users.IATestDate, Year([IATestDate]) AS Expr1
FROM Users
WHERE (((Users.OfcSym)<48) AND ((Year([IATestDate]))<Year(Date())))
ORDER BY Users.IndvName;

gets me everyone that got their training last year, but is not necessarily overdue, their month just hasn't come up yet.

If i do:

SELECT Users.IndvName, Users.OfcSym, Users.IATestDate, Month([IATestDate]) AS Expr1
FROM Users
WHERE (((Users.OfcSym)<48) AND ((Month([IATestDate]))<Month(Date())))
ORDER BY Users.IndvName;

then i get everyone who's month is less than the current month, regardless of training date.

i want to filter by Year([IATestDate])< Year(Date()) AND Month([IATestDate]) < Month(date()) with everyone who's training is one month or one year overdue to show up.

Thanks,
Dave

 

[Chad1984]

I think you might find the following page helpful:

http://office.microsoft.com/en-us/access/HA010546621033.aspx?pid=CL100570041033

Look at the "dateadd" function specifically. If I understand what you want correctly (and I may be confused) you want to view all people whose training dates are before one month ago, correct? Ifso, then I think you want something like:

WHERE [IATestDate] < DateAdd("m",-1,Date())

This should compare the test date to the current date, rewound by one month. Note that "DateAdd" is intelligent enough to allow for different months having different numbers of days; if it's the 30th and the previous month has 28 days, it will use the 28th of the previous month.

 

[wreded]

Chad1984,
What i'm trying to do is reliably find those folks that are really overdue. i can get everyone who's date is before the current year:

IndvName	OfcSym  IATestDate
User1           Ofc1    03-Dec-06 - Not yet overdue
User2           Ofc2    30-Dec-03 - Waaay Overdue
User3           Ofc1    15-Mar-05 - Overdue
User4           Ofc4    27-Sep-06 - Not yet overdue

i can reliably get previous, next, and current months and those folks that had their training prior to the current year, but what i'm having a harder time doing is getting those folks that fall outside of previous, next, and current month. They seem to fall between the cracks and i have to really search for them.

Thanks for the link. i'll let You know how i do.

Thanks,
Dave

 

[SgtJarrow]

I think Chad has you down the right path. DateAdd will be your friend in this case. Based on your description, you have a date field that shows the last training a person did. What you want to see is all persons who trained 13 months or more ago (1 year plus 1 month - means they completed their training last year or later at least a month ago or later from today).

Something like:

WHERE [IATestDate] < DateAdd("m",-13,Date())

If we have not understood your scenario, please provide a group of examples as you did, but also a sample of what you expect the output to be...(separate blocks so we can see the drops).


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer