troubleshooting Question

Need help changing a Microsoft Access select query having to show only dated records more than 7 days old.

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
Microsoft AccessVBASQL
3 Comments1 Solution69 ViewsLast Modified:
Need help with changing Microsoft Access SQL query to show date entered records that are more than 7 days old.
So far a lot of suggestions have not really worked for me at this link so I decided to add this as a new question:

https://www.experts-exchange.com/questions/29140112/Need-help-with-creating-a-cross-tab-report-that-would-identify-the-last-date-an-employee-entered-time.html?headerLink=workspace_answered_questions

I know must include : HAVING but no data is coming up with records that do have dates more than 7 days old.

SELECT Employees.EmployeeID, Employees.UserName, Max(TimeSheetData.DateEntered) AS MaxOfDateEntered
FROM Employees LEFT JOIN TimeSheetData ON Employees.EmployeeID = TimeSheetData.EmployeeID
GROUP BY Employees.EmployeeID, Employees.UserName
HAVING DateValue(Max(TimeSheetData.DateEntered)) > DateAdd("d",7,Date())
ORDER BY Max(TimeSheetData.DateEntered) DESC;

When I try the above I get DataType mismatch in criteria expression.

If I try below- I get no errors but I also get no records which can't be right cause 80% are over 7 days old

SELECT Employees.EmployeeID, Employees.UserName, Max(TimeSheetData.DateEntered) AS MaxOfDateEntered
FROM Employees LEFT JOIN TimeSheetData ON Employees.EmployeeID = TimeSheetData.EmployeeID
GROUP BY Employees.EmployeeID, Employees.UserName
HAVING Max(TimeSheetData.DateEntered)>DateAdd("d",7,Now())
ORDER BY Max(TimeSheetData.DateEntered) DESC;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros