Access 2003 Query return values that have

I am trying to create a query in MS Access to display users with training dates that are going to expire within 1 week (trainings expire a year from previous training date).  I am familiar with Oracle's date function and using truncate, however I am having issues finding the equivalent in MS Access.


SELECT Employees.Last_Name, Employees.First_Name, Employees.Dept, Employees.Last_Training_Date, Employees.Notes
FROM Employees
WHERE (([Last_Training_Date]< "yyyy", -1, Date()));
singi007Asked:
Who is Participating?
 
Patrick MatthewsCommented:
singi007,We're starting to get kind of far afield now with the follow-ups.  Your original question has been answered, and so I think it's time to close this.  One more for the road, see below.This uses the Switch function.  You could use embedded IIfs, but I prefer not to.  Please see my article on the subject:http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.htmlPatrick
SELECT Last_Name, First_Name, DEPT, Last_Training_Date, Notes,
Switch(DateAdd("d", 365, [Last_Training_Date]) < Date(), "Overdue", DateDiff("d", Date(), DateAdd("yyyy", 1, [Last_Training_Date])) <7, "Due within 7 days", True, "Due in 8-42 Days") AS Status
FROM Employees
WHERE DateAdd("d", 42, (DateAdd("yyyy", -1, Date()))) >= [Last_Training_Date]
order by Last_Training_Date;

Open in new window

0
 
NEVAEHSINCommented:
You can try...

[Last_Training_Date]<=DateAdd("yyyy", -1, Date)
0
 
singi007Author Commented:
It says "Data type mismatch in criteria expression" and I think that would just get me 1 year past, I need it to give me the users that are within 1 week of a year past due.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
NEVAEHSINCommented:
First off - is [Last_Training_Date] Date/Time format?

Next try....
 where DateAdd("yyyy",1,Last_Training_date)>DateAdd("d",-7,Date());
0
 
Patrick MatthewsCommented:
Catches those who are both overdue, and who have a week left:


SELECT Employees.Last_Name, Employees.First_Name, Employees.Dept, Employees.Last_Training_Date, Employees.Notes
FROM Employees
WHERE DateAdd("yyyy", -1, DateAdd("d", -7, Date())) <= [Last_Training_Date])

Open in new window

0
 
singi007Author Commented:
Yes, Last_Training_Date is a Date/Time format. The syntax

WHERE DateAdd("yyyy", -1, DateAdd("d", -7, Date())) >= [Last_Training_Date]

gives me those that are both overdue and who have a week left, but I want to differentiate between the two. Is it possible to break it into 2 queries? or some other method of displaying the 2 different groups?
0
 
Patrick MatthewsCommented:
Give this a try:
SELECT Employees.Last_Name, Employees.First_Name, Employees.Dept, Employees.Last_Training_Date, Employees.Notes,
    IIf(DateAdd("yyyy", 1, [Last_Training_Date]) > Date(), "Overdue", "Expiring Soon") AS Status
FROM Employees
WHERE DateAdd("yyyy", -1, DateAdd("d", -7, Date())) <= [Last_Training_Date])

Open in new window

0
 
singi007Author Commented:
Ok, I think from your solution I figured out how to get and differentiate the overdue employees and the overdue in the next 7 days employees. Is there anyway to use this IIf function to differentiate between 3 sets, for example the overdue employees, employees 7 days from being overdue and 6 weeks from being overdue? or does the IIf function only allow 2?
SELECT Employees.Last_Name, Employees.First_Name, Employees.DEPT, Employees.Last_Training_Date, Employees.Notes,
IIf(DateAdd("d", 365 , [Last_Training_Date]) < Date(), "Overdue", "Expiring within 7 Days") AS Status
FROM Employees
WHERE ( DateAdd("d", -358, Date())) >= [Last_Training_Date]
order by Last_Training_Date;

Open in new window

0
 
singi007Author Commented:
Thank you! This is exactly what I was trying to figure out. I thought it would be easy to figure out the rest once I had the first part, but then realized it wasn't as simple as I had hoped.
0
 
Patrick MatthewsCommented:
singi007,Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my articlehttp://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.htmland click 'Yes' for the 'Was this helpful?' voting.matthewspatrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.