Solved

Get records from last 7 days in access

Posted on 2010-08-26
4
687 Views
Last Modified: 2012-05-10
I need to filter my "Created Date" column in access by the last 7 days.  The date format is as follows MM/DD/YYYY HR:MIN:SEC AM/PM

any help is appreciated
0
Comment
Question by:KnutsonBM
[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
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33531292


select * from tablex
where datevalue([createdDate])<=Date()-7

or

select * from tablex
where datevalue([createdDate])<=DateAdd("d",-7,Date())

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33531308
it is the other way around



select * from tablex
where datevalue([createdDate])>=Date()-7

or

select * from tablex
where datevalue([createdDate])>=DateAdd("d",-7,Date())

0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 33531536
Just a comment to highlight that  the *format* of the date is not particularly relevant as that is only how the date is *displayed*. However as your date field does include the time of day that will effect the SQL that you need to select data from the last seven days.

The reason is that Date() returns just a bare date, with no apparent time value, but that is misleading because 8/26/2010 really means MIDNIGHT on 8/26/2010, ie. just after we moved from 8/25/2010.

That is why capricorn1 uses datevalue([createdDate]) in the SQL as datevalue gets rid of the time portion, and so you can be sure that records with any time value will get included as long as the date part is 7 days ago.
0
 
LVL 6

Author Closing Comment

by:KnutsonBM
ID: 33531618
Works great, had some issues for a minute until i realized i told you the wrong name, it wasnt createdDate, it was CreateDate
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

751 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