How to select most current date by month, day and year (3 Viewers)

[Design by Sue]

My datebase is tracking employee training, Each employee is required to complete the training every 12 months (for example) The database tracks all of the info about the employee and I have a table that keeps the records of all training completed dates for all employees for all SOP numbers (the training courses). I have created a report to show the employees that have expired training. I currently have ExpiredDate: DateAdd("m",[Expiration],[DateCompleted]) as my code but that I believe is only searching for the most current month but does not take into consideration the rest of the date. I need to select only the most recent of the dates based on the entire expiration date. Can someone please correct my code to show the results based on the entire date?

Thanks

 

[June7]

That expression is not "searching for the most current month", it is simply adding so many months as determined by Expiration to DateCompleted. It's not clear to me what you want.

 

[Gasman]

but that I believe is only searching for the most current month but does not take into consideration the rest of the date.

No, it just gives you a date, that the course needs to be renewed.

 

[Design by Sue]

Sorry - I was wrong in that! I have a report that shows the current date and I need the report to show only the SOPs that have expired as of the date on the report.

 

[Gasman]

I think you would need MAX() on the expiration date which is less than Date.
You could run a report each week that shows whose courses are going to expire in the following week/month?, being procactive, rather than waiting for the courses to expire.

 

[DHookom]

Consider creating a totals query that groups by employee and course with the max of the training date. Use this max date to determine if it is more than one year ago. A more complete answer would require the structure of your significant tables and fields.

 

[The_Doc_Man]

What you need is TWO fields in your individual training records. (At least two.) One of them is a flag that says "superseded" or something else that means that this old record has become irrelevant. If Joe Schmidlap takes "Operator Level 1" training and then a year later takes it again, you want ALL PREVIOUS RECORDS for "Joe Schmidlap" taking "Operator Level 1" to become superseded so that they don't figure into the "overdue" computation. The second field you need (and probably have) is the date on which the training occurred.

In a normalized setup, you would also have a table of available training and an expiration amount, which could be in any units you want. You would want a number and a unit. The unit would match the units used by DateAdd, which would include years, months, weeks, days, ... whatever. The amount would be the number of those units for expiration computation.

So then when doing the computation, you can do this either of two ways. EITHER

A. Compute the date of interest by using DateAdd( [units], -[expiration-amount], Date() ) to get the critical date
B. Compute the expiration date by using DateAdd( [units], [expiration-amount], [TestTakenDate] ) to get the critical date. Further, for (B) you must choose the only "test taken" record for that person and test combination for which the "superseded" flag is NOT set.

For (A), if the computed date is GREATER than the (only) non-superseded date that the test was taken, it is overdue.
For (B), if the expiration date is LESS than Date() (= today), it is overdue.

DateAdd function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Just watch out for "y" vs "yyyy" and "w" vs "ww" as the units in question. There is a reflexive tendency to use Now() as a date source, but Now() also includes time-of-day elements and thus could give you a confusing answer on the exact due date. That's why I specified Date() and not Now().

 

[Design by Sue]

I think you would need MAX() on the expiration date which is less than Date.
You could run a report each week that shows whose courses are going to expire in the following week/month?, being procactive, rather than waiting for the courses to expire.

Yes I have that report also an will be fixing that next. I will look up MAX and see if I can figure it out. Thanks

 

[Pat Hartman]

I think you want a query with this as the Where Clause

Where Max (DateAdd("m",[Expiration],[DateCompleted])) < Date()

If you want something other than today's date, use a different field in place of the Date()