Solved

Access 2007 Date Query Criteria -6 by date

Posted on 2014-09-10
7
528 Views
Last Modified: 2014-09-10
I need help with a query on one of my tables. On one of the table fields there is a date mm/dd/yyyy. I need to have the user input a future date and then count back 6 years from the table field date. I only want to see the dates that fall in the 6 year window.

 ex: user input - 06/01/2015

 the query would pull only the dates starting 06/01/2009 to 05/31/2010
0
Comment
Question by:CMILLER
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40315923
Use something like
WHERE DATE BETWEEN DATEADD("yyyy",-6, Date Passed) and DatePassed
0
 

Author Comment

by:CMILLER
ID: 40315927
Thats not working
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40315932
select [datefield]
from tablex
where [datefield] between dateadd("yyyy',-6,[enter Date]) And  dateSerial(Year([Enter Date]) - 5, Month([Enter Date]), 0)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:CMILLER
ID: 40315936
I am getting a "The expression you entered has an invalid string" A string can be up to 2048 char long, including opening and closing quotation marks
0
 

Author Comment

by:CMILLER
ID: 40315946
found it

("yyyy'
0
 

Author Comment

by:CMILLER
ID: 40315959
is my thinking correct on this?

If I am going back 6 yrs from a date ( 06/15/2015 ) would that be in between 6/01/2009 to 5/31/2010?

or is it between 5/31/2008 to 6/01/2009?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40315966
Not sure  - open the immediate window of VBA and type in the expression as ?Datevalue(with your details) and see what it returns.


Kelvin
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question