[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 919
  • 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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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