Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Problem (AXP SP-1)

    Does anyone know of a bit of code that would give me the beginning and ending dates of "last week"? I am trying to automate the printing of some labels than need to be printed weekly. I would like to click on a button that would determine the dates of "last week" and feed them to my query. I'm thinking this can be done using format(now(), "ww") somehow, but I can't figure it out. Anybody have any ideas?

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Problem (AXP SP-1)

    Assume Sunday is the first day of the week, Monday is the second, etc.
    intDayOfWeek is the day we are looking for. Sunday(first day of week) would be 1, Saturday(last day of week) would be 7.

    To return the date of the first day of last week, try this:
    dtmDate - Weekday(dtmDate) + intDayOfWeek -7
    If today is 10/09/02, and we want to know the date of the first day of last week'.
    #10/09/02# - Weekday(#10/09/02#) + 1 -7
    Returns 9/29/02

    To find the last day of last week, just change the 1 to 7
    #10/09/02# - Weekday(#10/09/02#) + 7 -7
    Returns 10/5/02

    Later,
    bruce

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Date Problem (AXP SP-1)

    One minor point to add to Bruce's excellent response - international date settings can create problems with this kind of logic, as Sunday may not be Day 1 in some locations. There is some additional info in the Help files and on the MS Knowledge Base about those issues.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Week Start (AXP SP-1)

    You can do it like this:

    <pre>Dim dtmWeekStart as Date
    Dim dtmLastWeekStart as date

    dtmWeekStart = DateAdd("d", -1 * Weekday(ThisDate), ThisDate)
    dtmLastWeekStart = DateAdd("d",-7,dtmWeekStart)</pre>

    To get the ending date of the week, use DateAdd to add 6 to the beginning date.

    I know this has been posted in the Lounge by someone before, but I couldn't find it so you're getting my version.

    BTW, this approach doesn't care which day the week starts on or what the date format is.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •