All of my entries have a due date. I am trying to run a query to pull everything that is due in 90 days from today. I thought that adding <=DateAdd("d", 90, Date()) in the date field it would query what i want but I am getting blank queries.
SELECT Roster.[Last Name], Roster.[First Name], Roster.Rank, Evaluation.[Draft Initiated], Evaluation.Location, Roster.[Eval Type], Roster.Evaluation, Roster.Rater, Roster.[Senior Rater], Roster.[Supp Reviewer]
FROM Roster INNER JOIN Evaluation ON Roster.[DoD ID] = Evaluation.[DoD ID]
WHERE (((Roster.Evaluation)<=DateAdd("y",90,Date())) AND ((Roster.Platoon) Like "3"));
What happens if you take out the "AND ((Roster.Platoon) Like "3"));" part just for troubleshooting? Do you still get a blank query?Code:SELECT Roster.[Last Name], Roster.[First Name], Roster.Rank, Evaluation.[Draft Initiated], Evaluation.Location, Roster.[Eval Type], Roster.Evaluation, Roster.Rater, Roster.[Senior Rater], Roster.[Supp Reviewer] FROM Roster INNER JOIN Evaluation ON Roster.[DoD ID] = Evaluation.[DoD ID] WHERE (((Roster.Evaluation)<=DateAdd("y",90,Date())) AND ((Roster.Platoon) Like "3"));
Several other things (beyond using "d" in the DateAdd expression):Code:SELECT Roster.[Last Name], Roster.[First Name], Roster.Rank, Evaluation.[Draft Initiated], Evaluation.Location, Roster.[Eval Type], Roster.Evaluation, Roster.Rater, Roster.[Senior Rater], Roster.[Supp Reviewer] FROM Roster INNER JOIN Evaluation ON Roster.[DoD ID] = Evaluation.[DoD ID] WHERE (((Roster.Evaluation)<=DateAdd("y",90,Date())) AND ((Roster.Platoon) Like "3"));
1. no its a number fieldSeveral other things (beyond using "d" in the DateAdd expression):
1. Is there a field called Platoon in the Roster table and, if so, is it a text field? If a number field, remove the quotes around "3"
2. Is the Evaluation field a DateTime datatype?
3. Are any records returned if you completely remove the WHERE clause?
4. Are there any matching ID values in the 2 tables?
SELECT Roster.[Last Name], Roster.[First Name], Roster.Rank, Evaluation.[Draft Initiated], Evaluation.Location, Roster.[Eval Type], Roster.Evaluation, Roster.Rater, Roster.[Senior Rater], Roster.[Supp Reviewer]
FROM Roster INNER JOIN Evaluation ON Roster.[DoD ID] = Evaluation.[DoD ID]
WHERE (((Roster.Evaluation)<DateAdd("d",91,Date())) AND ((Roster.Platoon) = 3));