Date Query (1 Viewer)

Aimn_4U

Registered User.
Local time
Today, 23:52
Joined
May 14, 2019
Messages
33
Hi all,

I am hoping someone can help as I am sure this is fairly simple but cannot seem to figure it out.

I have a "Date assessed" Section that is in my original table. In one of my queries I only want it to show data that is older than 2 years from today's date, and my other query I would like to show anything from 2 years ago until today's date.

Help would be greatly appreciated.

thank you
 

bob fitz

AWF VIP
Local time
Today, 15:52
Joined
May 23, 2011
Messages
4,717
Try the following expressions as criteria in your date assesed column:
>DateAdd("yyyy",-2,Date())
or
<DateAdd("yyyy",-2,Date())
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:52
Joined
Oct 29, 2018
Messages
21,360
Hi. I agree with Bob but try playing with both >= and <= as well.
 

Aimn_4U

Registered User.
Local time
Today, 23:52
Joined
May 14, 2019
Messages
33
Hi DB Guy and Bob,

Thank you both for your answers, however I still cannot get this to work.

I have put it in the Criteria part of the Query under the 'Date assessed' section and it still coming with dates from 2019 ?

Any further suggestions or Something I'm missing ?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:52
Joined
Feb 19, 2013
Messages
16,555
Any further suggestions or Something I'm missing ?
probably something you are missing. Suggest show the sql to your query that is producing the wrong result
 

Aimn_4U

Registered User.
Local time
Today, 23:52
Joined
May 14, 2019
Messages
33
I still cannot manage to get this to work :mad:

Below is my SQL, Could someone please let me know where I am going wrong before my head explodes :banghead:

SELECT tblUnitEquivalence.UnitEquivalence_ID, tblUnitEquivalence.[Internal Unit Code], tblUnitInternal.[Internal Unit Title], tblUnitEquivalence.[External Unit Code], tblUnitExternal.[External Unit Title], tblUnitExternal.[External Institution], tblUnitEquivalence.[Date Assessed]
FROM tblUnitInternal INNER JOIN (tblUnitExternal INNER JOIN tblUnitEquivalence ON tblUnitExternal.[External Unit Code] = tblUnitEquivalence.[External Unit Code]) ON tblUnitInternal.[Internal Unit Code] = tblUnitEquivalence.[Internal Unit Code]
WHERE (((tblUnitEquivalence.[Date Assessed])>DateAdd("yyyy",-2,Date())));
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:52
Joined
May 7, 2009
Messages
19,175
your query on post #6 gives you records 2 years ago upto today.

to get records older than 2 years, reverse your operator ("<=")
 

Aimn_4U

Registered User.
Local time
Today, 23:52
Joined
May 14, 2019
Messages
33
Hi ArnelGP,
I have tried this however when I open the query it is still showing all dates :confused:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:52
Joined
May 7, 2009
Messages
19,175
can you post a sample db, with some of your data.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:52
Joined
Feb 19, 2013
Messages
16,555
I have put it in the Criteria part of the Query under the 'Date assessed' section and it still coming with dates from 2019 ?


this statement brings through all records withing the last two years
Code:
WHERE (((tblUnitEquivalence.[Date Assessed])[B][COLOR="Red"]>[/COLOR][/B]DateAdd("yyyy",-2,Date())));

so meets your second requirement

I have a "Date assessed" Section that is in my original table. In one of my queries I only want it to show data that is older than 2 years from today's date, and my other query I would like to show anything from 2 years ago until today's date.

to get those more than two years old the criteria needs to be

WHERE (((tblUnitEquivalence.[Date Assessed])<=DateAdd("yyyy",-2,Date())));

Note: if your date_assessed field includes dates that are later than today then these will be returned by the query for your second requirement. If this is the case then the criteria needs to be

Code:
WHERE tblUnitEquivalence.[Date Assessed][B][COLOR="Red"]>[/COLOR][/B]DateAdd("yyyy",-2,Date()) AND tblUnitEquivalence.[Date Assessed][B][COLOR="Red"]<=[/COLOR][/B]Date()
 

Aimn_4U

Registered User.
Local time
Today, 23:52
Joined
May 14, 2019
Messages
33
Thank you Arnel GP and CJ_London both for your help.

I have managed to get the code working for the dates within the last 2 years, however I am still having issues with the one that picks up only dates older than 2 years.

I have put the attachments for the SQL, Datasheet and design view, so you can see exactly what i am doing and the outcome.

You can see in the datasheet that it is still picking up items from 2017, 2018, 2019.

I would only like it to pick dates that are older than September 2017.
Once this date goes past 2 years, these are put into another section of my database so the person knows that are due for a review.

I hope this all makes sense.

Thank you
 

Attachments

  • More than 2 years old - datasheet View.jpg
    More than 2 years old - datasheet View.jpg
    98.1 KB · Views: 92
  • More than 2 years old - Design View.PNG
    More than 2 years old - Design View.PNG
    34.8 KB · Views: 86
  • More than 2 years old - SQL view.PNG
    More than 2 years old - SQL view.PNG
    26.3 KB · Views: 80

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:52
Joined
May 7, 2009
Messages
19,175
is the field "date assessed" has datatype of Date/Time?
seems it is Short Text.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:52
Joined
Feb 19, 2013
Messages
16,555
first I would take out the input mask - not required and may be hiding the true position. For example the mask states the format is 99/00/0000 - two chars between the /. Yet your results are only showing one

second comment is the dates are left justified which implies they are text and not a date (which is numeric)

So we also need to see details of the field in the unitequivalence table

Also you have spaces in your field names which can cause problems - this may be one of them.Using square brackets usually solves the problem but I have seen the odd occasion where problems still exist because one of the words in the name is a reserved word (date in this case)
 

Users who are viewing this thread

Top Bottom