Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    getting a fiscal yr - mo and week (97)

    Is there a clever way of getting the month and week of a fiscal year when the fiscal year say starts on AUG 31, there may be 4 or 5 weeks in a fiscal month, and the weeks are full weeks (sun through sat)?
    Aug 31 - Sept 6, 2003 = month 1, week 1
    Sept 28 - Oct 4, 2003 = month 1, week 5
    Oct 5 - Oct 11, 2003 = month 2, week 1
    Thanks for looking. John

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    John,
    Give this SQL a try. You can test it for all dates by creating a table with dates from 8/31/03 - 8/30/04, naming the field MyDate and referencing the field in the SQL to the field from your table. Let me know if you have any questions.
    Dashiell

    >>>>>>>>>.
    SELECT #10/5/2003# AS MyDate, "Month " & IIf([myDate]+1-Weekday([mydate])=#8/31/2003#,1,DatePart("m",([myDate]-Weekday([mydate])+1))-8) & ", Week " & IIf([myDate]+1-Weekday([mydate])=#8/31/2003#,"1",Left((DatePart("d",[myDate]+1-Weekday([mydate]))/7),1)+1) AS MonthWeek;

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    John,
    There was one slight bug that would cause the calc to fail for the month after 12/21/03. Attached is the correct version. Pelase keep in mind, it works only for fiscal year beginning 8/31/03 and it helps that 8/31 falls on a Sunday this year so it fits your logic perfectly. You will have to re-work the calc if your year begins on a Wednesday (e.g., would Wed. - Sat. be week one and week 2 begins the next Sunday...etc.) Here is the revised SQL:

    >>>>>>>>>>>
    SELECT #8/31/2003# AS MyDate, "Month " & IIf([myDate]+1-Weekday([mydate])=#8/31/2003#,1,DateDiff("m",#8/31/2003#,([myDate]-Weekday([mydate])+1))) & ", Week " & IIf([myDate]+1-Weekday([mydate])=#8/31/2003#,"1",Left((DatePart("d",[myDate]+1-Weekday([mydate]))/7),1)+1) AS MonthWeek;

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    Hi Dashiell -
    I am not sure where to put this SQL statement. I tried a query but it wouldn't run. I am also not sure I gave you enough information either.
    I have records with dates. I wanted to group these records by month and then group them by week in a report. Would I need to add a field in a query that would look at the date field and return a month number and then another field that would return a week number? I could then build a report based on that query.
    The SQL looked impressive. I am still looking at it trying to figure out the calculations and what it is doing.
    I am still a newbie.
    Thanks in advance.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: getting a fiscal yr - mo and week (97)

    What are the exact criteria? For instance, is November 30, 2003 (a Sunday), in week 5 of month 3, or in week 1 of month 4? How do you decide?

    Dashiell's SQL probably does what you want. You can use these expressions, directly lifted from his post, but with a fictitious field name dtmDate substituted:

    Month: IIf([dtmDate]+1-Weekday([dtmDate])=#8/31/2003#,1,DateDiff("m",#8/31/2003#,([dtmDate]-Weekday([dtmDate])+1))

    Week: IIf([dtmDate]+1-Weekday([dtmDate])=#8/31/2003#,"1",Left((DatePart("d",[dtmDate]+1-Weekday([dtmDate]))/7),1)+1)

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    John,
    Not a problem and Hans gave you right-on advice, you will need to break out the Month and Week fields. As far as the SQL, follow the steps below:
    1 - Create a new Query in Design Mode and do not add a table (click Close at the Show Table screen
    2 - You will then see a blank query screen with SQL in bold black where you normally see the Design View or Worksheet View buttons
    3 - Click the SQL icon
    4 - Copy and paste the following code in the blank void:
    SELECT #8/31/2003# AS MyDate, "Month " & IIf([myDate]+1-Weekday([mydate])=#8/31/2003#,1,DateDiff("m",#8/31/2003#,([myDate]-Weekday([mydate])+1))) AS [Month], "Week " & IIf([myDate]+1-Weekday([mydate])=#8/31/2003#,"1",Left((DatePart("d",[myDate]+1-Weekday([mydate]))/7),1)+1) AS Week;
    5 - Click the Datasheet View icon and then revert back to the design view
    6 - You may then add your table and change the reference of MyDate:#08/31/2003# to MyDate:[The Date Field from the table you added]
    This should then create two fields: Month and Week based on the dates you are pulling.
    Give it a try and let me know how it works.
    Dasheill

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    Hi Dashiell-
    I copied and pasted the SQL that you posted into the SQL screen but when I go to datasheet view I get an error. "The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"
    Real helpful. LOL
    I will try Hans suggestion in the morning and review your post again. This is great.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    Interesting...I just copied it from my post and it worked fine. Did you delete the 'Select' statement in the query before pasting it? If you still have probelms, I can send you a file.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    Attached is a file with the answer. Keep in mind, never name a field something Access reserves for internal usage (e.g. Name or Date) since Access reserves these names for specific purposes.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    When I pasted the SQL statement in the SQL box, the 'select' was highlighted so I pasted over it. I also copied the statement from the email instead of from the lounge and got the same error.
    When I try to open your database I get a unrecognized database format. Did you save it for Access 97?
    I am sorry to be so much trouble. Thanks for working with me.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: getting a fiscal yr - mo and week (97)

    What is the name of the date field in your table?

  12. #12
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    Hi Hans -
    The week expression was accepted by Access but the month expression was not - Access said I was missing a closing parenthesis, bracket, or vertical bar.
    The criteria for deciding whether the end of the calendar week is in the same fiscal month or the start of the next fiscal month is : If there are 4 or more calendar days in the last week of the month, that week we be in that month's fiscal month. If there are 3 or less calendar days in the last week of the calendar month, those days would be the start of the first week of the following month.
    So Nov 30, 2003 would be the first day of the first week in Dec. (month 4). The expression you provided returned week 5.
    Thanks for your patience and help.

  13. #13
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    The name of the date field is DT. I replaced the datefield in your expression with DT and got the week expression field to work but not the month. See previous post. Thank you very much.
    Best Wishes,
    John

  14. #14
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting a fiscal yr - mo and week (97)

    Sorry, I should have noticed the version in your original post. This should do the trick...let me know how it goes.
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: getting a fiscal yr - mo and week (97)

    The expressions I posted were based on Dashiell's SQL. He uses a different premise than you: for a given date, look at the last Sunday on or before that date. The month of this Sunday is the month of the given date. So December 6 (a Saturday) is in the fifth week of November, since the previous Sunday is November 30.

    There must be very clever formulas to calculate the week and month according to the system you use, but that is beyond me. I wrote two VBA functions to do it; they should go into a standard module:

    Public Function FiscalWeekNum(aDate)
    Dim datDay4 As Date, datWeek1 As Date
    Dim intYear As Integer, intMonth As Integer, intDayOfWeek As Integer
    If IsNull(aDate) Then
    FiscalWeekNum = Null
    Exit Function
    End If
    intYear = Year(aDate)
    For intMonth = Month(aDate) + 1 To Month(aDate) - 1 Step -1
    datDay4 = DateSerial(intYear, intMonth, 4)
    intDayOfWeek = Weekday(datDay4)
    datWeek1 = datDay4 + 1 - intDayOfWeek
    If aDate >= datWeek1 Then Exit For
    Next intMonth
    FiscalWeekNum = (aDate - datWeek1) 7 + 1
    End Function

    Public Function FiscalMonthNum(aDate)
    Dim datDay4 As Date, datWeek1 As Date
    Dim intYear As Integer, intMonth As Integer, intDayOfWeek As Integer
    If IsNull(aDate) Then
    FiscalMonthNum = Null
    Exit Function
    End If
    intYear = Year(aDate)
    For intMonth = Month(aDate) + 1 To Month(aDate) - 1 Step -1
    datDay4 = DateSerial(intYear, intMonth, 4)
    intDayOfWeek = Weekday(datDay4)
    datWeek1 = datDay4 + 1 - intDayOfWeek
    If aDate >= datWeek1 Then Exit For
    Next intMonth
    FiscalMonthNum = (intMonth + 3) Mod 12 + 1
    End Function

    Use as follows: in the query grid, add calculated fields

    FiscalWeek: FiscalWeekNum([DT])

    and

    FiscalMonth: FiscalMonthNum([DT])

Page 1 of 2 12 LastLast

Posting Permissions

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