Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2016
    Posts
    2

    Unanswered: MS Access ISO Week no

    how would I get MS access to by default work according to the ISO week calendar.
    ie: Jan 1 2016 should be week 53 instead of week 1.
    The above function should not be limited to one field or one particular query.
    The function should be uniform across the database and should be applicable to all the date fields(i.e: if i choose to get the week nos out of the date fields)
    Is this possible?

    MS ACCESS VERSION : 2010

    Please help

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use datetpart, but specify:-
    the start of the week if you NLS settigns are not alreeady set to Monday
    the first week of the year argument

    so thats going to be something like
    Code:
    =datepart("ww", mydatecolumn, 2,3)
    or using the constants
    Code:
    =datepart("ww", mydatecolumn, vbMonday, vbFirstFullWeek)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2016
    Posts
    2

    Ms access ISO week no

    Thanks for the reply.One question.Do I create this as a module?
    It should actually affect all the date fields.Would it?If not how do i go about it?

    Sorry if I sound ignorant.

    Thanks for the help in advance

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    datepart is a VBA function, ie its available in any query, form, report. when you need the weeknumber, call it
    use it to return the week number
    you will need to check it gives the correct values and yiu may need to modify it to suit
    if you chose to modify it by creating say a wrapper function then that wrapper function should be in a code module, and then that function will be available in any form, query or report
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    H

    to calculate the ISO Week No you can use this

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

    Basically weeks start on Monday and week one contains the first Thursday in the year. That is the first week contains at least 4 days of that year (hence vbFirstfourDays).

    MTB

Posting Permissions

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