Results 1 to 5 of 5
  1. #1
    zebra12 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Location
    USA
    Posts
    2

    Query to determine fiscal week in a query based on date

    am working on an Access 2010 database.
    Each record has a date stamp. I would like the query to output the date with a corresponding week number.
    I would like this information to link to a table with the date at the start of the week, the date at the end of the week.
    for example, the table would look like this:
    ID: 1
    wkBegin: 12/29/14


    wkEnd: 1/4/15
    ID: 2
    wkBegin: 1/5/15
    wkEnd: 1/11/15
    When linking to the table with my dates, I have two examples of dates: 12/30/14 and 1/10/15
    I would like the output to look like this
    Wk: 1 Date: 12/30/14
    Wk: 2 Date: 1/10/15
    I know I would need to have the two tables linked in a query.
    My query will pull down the fields for weeknumber, date
    How would I go about doing this?

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    The trick to doing this is to determain first Week #1 of the year required. Once you have done that.... it is as easy as taking that date and adding 7 days (per week) * 14 weeks (as per your example) to Week #1 and you have your answer...
    Examples:
    CurWeek: DatePart("ww",Date())
    CurMonth: Format(DateSerial(Year(Date()),Month(Date()),Day(D ate())),"mmmm ")
    CurYear: DateSerial(Year(Date()),1,1)

    HTH

  3. #3
    zebra12 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Location
    USA
    Posts
    2
    Thank you for the suggestion.

    I think I am a lot closer now.

    I only need the fiscal week. If I want the fiscal week to start on September 29, 2014, how do I adjust the criteria you just gave me?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    The only way I have been able to accomplish what, I believe, you are trying to do, is to store dates in tables that have a corresponding integer to represent weekdays and months. You can do a similar thing using expressions in queries but the integer is only a first step to counting periods. You will need to use additional code to count periods. This is an approach I use for the reporting of FIFO and 4-4-5 inventory stuff.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    As burrina shows, DatePart function can extract a week number. Can use that value for grouping and sorting.

    DatePart("ww", Date(), vbMonday, vbFirstFullWeek)

    Or can calculate the Monday of the week for any given date and group/sort on that date. Review http://msdn.microsoft.com/en-us/libr...ffice.14).aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 10-18-2013, 03:14 PM
  2. Replies: 9
    Last Post: 04-26-2013, 12:12 PM
  3. Replies: 2
    Last Post: 03-07-2013, 03:14 PM
  4. Fiscal Quarters in a query
    By brownsugar7210 in forum Queries
    Replies: 10
    Last Post: 12-06-2012, 01:14 PM
  5. Replies: 4
    Last Post: 07-12-2011, 09:49 AM

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