locked
Microsoft Access 2010 (14.0.7162.5001) SP2 MSO (14.0.7166.5000) Week of The Year Error RRS feed

  • Question

  • Today is 06-28-2016 which is week 26.

    Using the TodaysWeek: DatePart("ww",Date()) returns '27'.

    Any ideas?


    Yo

    Tuesday, June 28, 2016 6:50 PM

Answers

  • In the USA system of week numbering, today is week 27.

    In the ISO system of week numbering, it is week 26. To get the ISO week number, use the following in VBA:

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

    And in an expression in a query or in the Control Source of a text box:

    =DatePart("ww", Date(), 2, 2)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 28, 2016 7:33 PM
  • >>>Today is 06-28-2016 which is week 26.

    Using the TodaysWeek: DatePart("ww",Date()) returns '27'.<<<

    According to your description, the ISO standard is for week 1 to be the one containing 4 days of January, or the first Thursday of the year (different ways of expressing the same thing). You can specify this method of calculation and the first day of the week:

    Format(yourDate,"ww",vbMonday,vbFirstFourDays)

    For more information, click here to refer about Format Function

    In addition when you use either the Format or DatePart function to determine the week number for dates using the following syntax:

    Format(AnyDate, "ww", vbMonday, vbFirstFourDays)

    DatePart("ww", AnyDate, vbMonday, vbFirstFourDays)

    the last Monday in some calendar years is returned as week 53 when it should be week 1.

    You could use a user-defined function to return the Week number based on the rules for the ISO 8601 standard, refer to BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year

    Wednesday, June 29, 2016 5:56 AM

All replies

  • What is the source of ‘TodaysWeek’?


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, June 28, 2016 7:30 PM
  • In the USA system of week numbering, today is week 27.

    In the ISO system of week numbering, it is week 26. To get the ISO week number, use the following in VBA:

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

    And in an expression in a query or in the Control Source of a text box:

    =DatePart("ww", Date(), 2, 2)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 28, 2016 7:33 PM
  • >>>Today is 06-28-2016 which is week 26.

    Using the TodaysWeek: DatePart("ww",Date()) returns '27'.<<<

    According to your description, the ISO standard is for week 1 to be the one containing 4 days of January, or the first Thursday of the year (different ways of expressing the same thing). You can specify this method of calculation and the first day of the week:

    Format(yourDate,"ww",vbMonday,vbFirstFourDays)

    For more information, click here to refer about Format Function

    In addition when you use either the Format or DatePart function to determine the week number for dates using the following syntax:

    Format(AnyDate, "ww", vbMonday, vbFirstFourDays)

    DatePart("ww", AnyDate, vbMonday, vbFirstFourDays)

    the last Monday in some calendar years is returned as week 53 when it should be week 1.

    You could use a user-defined function to return the Week number based on the rules for the ISO 8601 standard, refer to BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year

    Wednesday, June 29, 2016 5:56 AM