Solved

Access 2003 Query return values that have

Posted on 2010-09-10
10
392 Views
Last Modified: 2013-11-28
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()));
0
Comment
Question by:singi007
  • 4
  • 4
  • 2
10 Comments
 
LVL 1

Expert Comment

by:NEVAEHSIN
Comment Utility
You can try...

[Last_Training_Date]<=DateAdd("yyyy", -1, Date)
0
 

Author Comment

by:singi007
Comment Utility
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
 
LVL 1

Expert Comment

by:NEVAEHSIN
Comment Utility
First off - is [Last_Training_Date] Date/Time format?

Next try....
 where DateAdd("yyyy",1,Last_Training_date)>DateAdd("d",-7,Date());
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 

Author Comment

by:singi007
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 

Author Comment

by:singi007
Comment Utility
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 125 total points
Comment Utility
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
 

Author Comment

by:singi007
Comment Utility
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
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now