Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Access, show first day of week associated with a date

    Have a date field [Pass1] in query that I want to use to calculate first day of week associated with the Pass1 dates. Currently using FORMAT to create a Work-Week field in the query with expression
    WW: Format([pass1],"ww")

    Have been unable to get expression correct to also create a Week OF field in query
    WO: Format([pass1]????????????
    Looking for result: 1/12/11 to display Week Start Jan 10, 2011

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Here's one possible solution. Of course you can combine what I did in 2 steps into one for your purposes. My calculations assume Sunday is the 1st day of the week. If you want Monday change the subtraction in DOW from 1 to 2.

    Note WDate is defined as Date/Time.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks, that solved the problem

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is an alternative.

    The Weekday function accepts a second parameter to indicate what day of the week is the first. (Sunday is the default)
    Weekday([wdate],2) treats Monday as the first day of week.

    SOW: [Wdate]-Weekday([wdate],2) +1 is another expression for the start of the week. From each day go back its day of the week, then add 1 as you have gone 1 day too many.
    Regards
    John



  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    John,

    Definitely more elegant than my kludge. Thanks.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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