Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-09-2019, 12:03 AM   #1
Aimn_4U
Newly Registered User
 
Join Date: May 2019
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Aimn_4U is on a distinguished road
Date Query

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

Aimn_4U is offline   Reply With Quote
Old 09-09-2019, 12:48 AM   #2
bob fitz
AWF VIP
 
Join Date: May 2011
Location: Essex England
Posts: 3,489
Thanks: 50
Thanked 678 Times in 661 Posts
bob fitz has a spectacular aura about bob fitz has a spectacular aura about bob fitz has a spectacular aura about
Re: Date Query

Try the following expressions as criteria in your date assesed column:
>DateAdd("yyyy",-2,Date())
or
<DateAdd("yyyy",-2,Date())
__________________
Bob Fitzpatrick
Microsoft Community Contributor Award (2011)
If this helped, please click the scales at the bottom left of this posting.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Many thanks.


I have never failed. I have found a thousand ways that do not work!
bob fitz is offline   Reply With Quote
Old 09-09-2019, 04:28 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,611
Thanks: 50
Thanked 1,053 Times in 1,034 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Date Query

Hi. I agree with Bob but try playing with both >= and <= as well.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Tera (09-24-2019)
Old 09-16-2019, 12:32 AM   #4
Aimn_4U
Newly Registered User
 
Join Date: May 2019
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Aimn_4U is on a distinguished road
Re: Date Query

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 ?
Aimn_4U is offline   Reply With Quote
Old 09-16-2019, 12:55 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,237
Thanks: 40
Thanked 3,645 Times in 3,515 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Date Query

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-22-2019, 07:53 PM   #6
Aimn_4U
Newly Registered User
 
Join Date: May 2019
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Aimn_4U is on a distinguished road
Re: Date Query

I still cannot manage to get this to work

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

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())));
Aimn_4U is offline   Reply With Quote
Old 09-22-2019, 08:14 PM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,401
Thanks: 68
Thanked 2,703 Times in 2,588 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Date Query

your query on post #6 gives you records 2 years ago upto today.

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 06:10 PM   #8
Aimn_4U
Newly Registered User
 
Join Date: May 2019
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Aimn_4U is on a distinguished road
Re: Date Query

Hi ArnelGP,
I have tried this however when I open the query it is still showing all dates
Aimn_4U is offline   Reply With Quote
Old 09-24-2019, 12:06 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,401
Thanks: 68
Thanked 2,703 Times in 2,588 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Date Query

can you post a sample db, with some of your data.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-24-2019, 01:18 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,237
Thanks: 40
Thanked 3,645 Times in 3,515 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Date Query

Quote:
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])>DateAdd("yyyy",-2,Date())));
so meets your second requirement

Quote:
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]>DateAdd("yyyy",-2,Date()) AND tblUnitEquivalence.[Date Assessed]<=Date()
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-24-2019, 05:28 PM   #11
Aimn_4U
Newly Registered User
 
Join Date: May 2019
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Aimn_4U is on a distinguished road
Re: Date Query

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
Attached Images
File Type: jpg More than 2 years old - datasheet View.jpg (98.1 KB, 12 views)
File Type: png More than 2 years old - Design View.PNG (34.8 KB, 8 views)
File Type: png More than 2 years old - SQL view.PNG (26.3 KB, 8 views)
Aimn_4U is offline   Reply With Quote
Old 09-24-2019, 06:08 PM   #12
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,401
Thanks: 68
Thanked 2,703 Times in 2,588 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Date Query

is the field "date assessed" has datatype of Date/Time?
seems it is Short Text.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-24-2019, 06:16 PM   #13
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,237
Thanks: 40
Thanked 3,645 Times in 3,515 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Date Query

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)

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to format date values in query criteria to search for short date MilaK Queries 16 04-19-2017 12:01 AM
Query Table1 date to Table2 column1 begin date range to column2 end date range shardez Queries 10 03-10-2014 12:14 PM
Query criteria Date() not accurate for fields set as General Date (date/time) isaacski Queries 3 05-24-2013 06:51 PM
need help making a query(s) to categorize time between ship date and repair date weeblebiker Queries 1 04-04-2013 10:05 AM
Calculating a review date based on a date field and a non date field in a query Sweetnuff38 Queries 4 10-15-2010 03:49 PM




All times are GMT -8. The time now is 05:12 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World