Results 1 to 4 of 4
  1. #1
    baba is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    43

    Select last weeks record by this weeks Monday date

    I would like to select records that
    are between last week's Monday and Sunday .I should be able to run it during any day of this week.
    Is it possible to get a query that does Something like below
    Select * from table where (This Week's Monday date - Table.RecordDate) < 8 ;


    This query should fetch last weeks data irrespective of whatever day I run the query hthis week

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

  3. #3
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    Here are links to the date functions which you need.

    http://www.ozgrid.com/Excel/day-of-week.htm
    http://www.classanytime.com/mis333k/sjdatetime.html

    In the criteria as you proposed, this week's Monday date would be given by:
    DateAdd("d",-weekday(Now(),3),Now())

    The chance of that being error free is about 5%

  4. #4
    baba is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    43
    Quote Originally Posted by hertfordkc View Post
    http://www.ozgrid.com/Excel/day-of-week.htm
    http://www.classanytime.com/mis333k/sjdatetime.html

    In the criteria as you proposed, this week's Monday date would be given by:
    DateAdd("d",-weekday(Now(),3),Now())

    The chance of that being error free is about 5%

    Thanks it works fine !!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Comparing consecutive weeks data
    By foamcows in forum Queries
    Replies: 1
    Last Post: 08-16-2011, 08:20 PM
  2. Date Add 26 weeks minus 1 day
    By ker9 in forum Queries
    Replies: 3
    Last Post: 07-26-2011, 01:20 PM
  3. Group Result by weeks
    By Grooz13 in forum Queries
    Replies: 2
    Last Post: 09-14-2010, 07:09 AM
  4. Date Parameter Help - prior 13 weeks
    By acw1980 in forum Access
    Replies: 1
    Last Post: 11-12-2009, 10:30 AM
  5. How to calculate number of weeks
    By FeatherDust in forum Queries
    Replies: 0
    Last Post: 09-18-2009, 02:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums