Query everything within a date (1 Viewer)

Valentine

Member
Local time
Today, 19:37
Joined
Oct 1, 2021
Messages
261
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:37
Joined
Oct 29, 2018
Messages
21,358
Can you post your query's SQL statement?
 

Valentine

Member
Local time
Today, 19:37
Joined
Oct 1, 2021
Messages
261
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"));
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:37
Joined
Oct 29, 2018
Messages
21,358
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"));
What happens if you take out the "AND ((Roster.Platoon) Like "3"));" part just for troubleshooting? Do you still get a blank query?
 

isladogs

MVP / VIP
Local time
Today, 23:37
Joined
Jan 14, 2017
Messages
18,186
Do you realise your DateAdd expression uses "y" not "d"?
Try correcting that!
If that fails, then perhaps the dates contain a time component?
If so, try <DateAdd("d", 91, Date())
 

Valentine

Member
Local time
Today, 19:37
Joined
Oct 1, 2021
Messages
261
yeah still got nothing, I changed to y after d didnt work and just didnt go back before copy pasting to here that was my bad.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:37
Joined
Sep 21, 2011
Messages
14,048
I tested with "y", and whilst confusing to me, it still adds days. :)
Get rid of the join purely for debugging?
 

isladogs

MVP / VIP
Local time
Today, 23:37
Joined
Jan 14, 2017
Messages
18,186
"y" references current day of year so the number of days added will increase by 1 each day.
 

isladogs

MVP / VIP
Local time
Today, 23:37
Joined
Jan 14, 2017
Messages
18,186
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):
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?
 

Valentine

Member
Local time
Today, 19:37
Joined
Oct 1, 2021
Messages
261
Several 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?
1. no its a number field
2. yes
3. I only get entries that have entries in both tables, some entries will not have ALL the information filled out
4. DoD ID is the same on all my tables thats how i link my queries
 

isladogs

MVP / VIP
Local time
Today, 23:37
Joined
Jan 14, 2017
Messages
18,186
Remove the "" around 3 and use = instead of Like
Try the following

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("d",91,Date())) AND ((Roster.Platoon) = 3));
 

Valentine

Member
Local time
Today, 19:37
Joined
Oct 1, 2021
Messages
261
I am sorry y'all. After sleeping on it i realized my issue, the table "Evaluation" only had 4 entries in it because i was testing so of course there was only that many things to output in the query.....So if anyone wanted to know the string i used for my dates.
>Date() And <=DateAdd("d",30,Date()) for everything upcoming within 30 days. I also went with a late query of <Date()
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:37
Joined
Feb 19, 2002
Messages
42,981
ALSO --- LIKE is a string operator and is used with wildcards. If you have a complete value, i.e. 3, you would NEVER use LIKE. The proper relational operator is =. Therefore, the condition would be
AND Roster.Platoon = 3

Another thing to keep in mind about LIKE, is that it almost always prevents the query engine from optimizing the query by using indexes so when you use LIKE, the query engine is forced to examine each row in the query RBAR (row by agonizing row). So, use LIKE if you have to search by a partial value but do not use it lightly since it is a very expensive operation.
 

Users who are viewing this thread

Top Bottom