Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Unanswered: Format Date Problem?

    Hey there,
    I seem to have stumbled on a wierd problem in an access report. The report sorts by week number by doing a format like so:
    Code:
    =Format$([LeaveDate],"ww")
    However, it skips the first week of January in the report and calls it week 2.

    Am I missing something here? Isn't the first week number of the month have to be an entire week? Turns out, the report is telling me that the following:

    1/3/2005 is week 2....

    Any insights? Is this a known issue?

    thx for your time.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    no, ive found the weeks run sunday to sunday and since the 1st of december is a saturday hence the 1/3/2005(i presume this is m/d/yyyy) is week 2,

    i think in the format function you can specify the first day of the week but as you get into the year you would want weeeks running Sunday to Sunday

    HTH
    Dave

  3. #3
    Join Date
    Oct 2003
    Posts
    73
    thx for the reply Dave.

    well, now i'm getting even more confused. According to this:
    http://www.iso.org/iso/en/prods-serv...ime.html#three
    the first day of the week is monday. Also, if you go to this site:
    http://www.personal.ecu.edu/mccartyr/isowdcal.html
    it will also tell you that Jan 3/2005 is the first week of january.

    So, what I have to do is figure out what standard MS is using. Why can't we all get along and use the same freaken standard! oh, thats right, it would be too easy then and we can't have that....

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There's an optional flag you can use to specify the first day of the week. I had to wrestle with this one not too long ago...

    Try something like this:

    Code:
    Public Function GetWeek(yourDate As Date) As Integer
    Dim d As Integer
    
    d = DatePart("w", (Month(yourDate) & "/1/" & year(yourDate)))
    
    GetWeek =  DatePart("ww", yourDate, d)
    End Function
    Here's another one I just thought of...
    Code:
    Int(day(yourDate) / 7) + Int(IIf(day(yourDate) Mod 7 > 0, 1, 0))
    Could've been worse... at least you didn't have to derive a date given a week and day number. *shudder*
    Last edited by Teddy; 11-30-04 at 16:34.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    DatePart has two optional parameters when you use 'w' (weekday) or 'ww' (week). The first is the first day of the week. You can set it to anything, I have a client that Friday is the first day of the week. The second optional parameter is the first week of the year. It can be set to three different things, one being the week that the 1st is in, another is the first full week in January. With the combination of those two parameters you should be able to do what you want.

  6. #6
    Join Date
    Oct 2003
    Posts
    73
    thx for all the replies. I went to msdn too and found the specs on the datepart function:
    http://msdn.microsoft.com/library/de.../d6/s5b208.asp
    However, when i specify the following: datepart("ww", 12/30/2004, 1,2), it would not display week 53!! However, to make it work for our situation here (we only work monday to saturday), this works for us: datepart("ww", 12/30/2004, 2,2) and returns the proper results (53) and thereafter as well. However, our calendars are numbered with the first day of the week being sunday...but oh well.

    Hope that made sense.

    Still kind of confused as to why i cannot specify datepart("ww", 12/30/2004, 1,2) and have it display week 53, but I got it working close enough now.

    Thx

Posting Permissions

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