Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 995
  • Last Modified:

Get records from last 7 days in access

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
KnutsonBM
Asked:
KnutsonBM
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:


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

or

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

0
 
Rey Obrero (Capricorn1)Commented:
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
 
LambertHeenanCommented:
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
 
KnutsonBMAuthor Commented:
Works great, had some issues for a minute until i realized i told you the wrong name, it wasnt createdDate, it was CreateDate
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now