none
Calculate week numbers in query

    Question

  • In my Tasks table, I got four fields:

    1. ID
    2. Task
    3. Start Date
    4. End Date 

    The start and end dates can be set freely. One task will normally last between one day and several weeks. E.g the task "Make something" with Start Date 01.12.11 and End Date 24.02.11 is active in week number 5, 6, 7 and 8.

    I would like to create a calculated field in a query called Task Week Numbers. The expression needs to calculate every week number in which the given task is active, based solely on the Start and End date.

    The query will populate the week numbers one by one in a list box called List Week Numbers, and the task named "Make something" will show up in the filtered Task Subform if I select either week 5, 6, 7 or 8.

    I have searched for the subject, but most of the examples seem to be written in VBA code, which is not applicable in this Access 2010 web application. I was hoping someone in this prima forum could assist me in building an expression containing the necessary algebra?

    EDIT: The week numbers need to be ISO (gregorian calendar), first week day monday.


    TEL
    • Edited by Toby Honest Monday, January 24, 2011 1:14 PM additional info
    Monday, January 24, 2011 1:12 PM

All replies

  • hi,

    you may use the DatePart() function in a query, e.g.

    DatePart(“ww”;[StartDate])

    But..

    EDIT: The week numbers need to be ISO (gregorian calendar), first week day monday.

    ..this was the problem in the past versions: it didn't return the ISO week number. I'm not sure if this is still true for Access 2010.

    But as you're using SharePoint to host the application, you should use a SharePoint calculated column, something like

    http://learning-dot-net.blogspot.com/2009/02/sharepoint-iso-week-number.html


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, January 24, 2011 1:24 PM
  • Hello Stefan, and thank you for taking your time to answer my question.

    When I open the expression builder in my query designer, I got only two Date/Time functions to choose from: Date and DateSerial. I tried an expression containing the sentence:

    Date("ww";[Start Date])

    This returns the following error when trying to run the query: "The expression you entered has a function containing the wrong number of arguments."

    As you probably understand, my espression building skills are at absolute rookie level...


    TEL
    Monday, January 24, 2011 1:47 PM
  • hi,

    DatePart is a VBA function, thus it may be not available when published to SharePoint. But as I wrote, it should not return the ISO calendar week..

    But you may simply try it in an unpublished test mdb.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, January 24, 2011 1:59 PM
  • OK, I think I need a different approach, Access is now warning me that the DatePart function is not working in the web version. I'm also having difficulties translating the Sharepoint code in your link to something I can use in an Access web expression...

    I'm sorry that I don't have the necessary skills to make good use of your answers, but Thanks anyway :)


    TEL
    Monday, January 24, 2011 2:27 PM
  • OK, I'll try to work out the logic based on the available functions in the expression designer, and for the sake of simplicity I will drop the ISO week number for this attempt.

    1. loop through every Date in the interval [Start Date, End Date].
    2. For each Date, return Month integer 
    3. For each Date, return Day integer
    4. For each Month, Day: (Month*4/(Day/4))
    5. Result is Week Number for each Date

    Is my logic any logic at all?


    TEL
    Tuesday, January 25, 2011 8:13 AM
  • Hello,

    When you’re in query design mode and you add a field, if you right click on the field and click properties you’ll see a Format property line.  Place the ww in the format property of a date field and run the query.  

    Since you’re working with weeks, remember there are 365 days in a year, 52 – 7 day periods, and always 53 weeks in a Gregorian calendar.  A week in a Gregorian calendar does not always consist of 7 days.

    Tuesday, January 25, 2011 6:48 PM
  • hi h2fcell,

    I'm sorry, but I have checked and I can't find the Format property in any date field in my querys... Are you sure this property also applies for the web database application in Access?


    TEL
    Wednesday, January 26, 2011 9:27 AM
  • Hi

    I know next to nothing about A2010, I'm still using A2003.

    I could be wrong and please excuse me for butting in if I am but I thought the seperator in Stefan's earlier post

    DatePart(“ww”;[StartDate])

    should be a commer, not a semi colon. eg

    DatePart(“ww”,[StartDate])

    In A2003 using the semi colon gave me an error msg, but using the commer worked.

    Just thought I'd mention this in case Stefan had made a typo.


    Bob Fitz BizSoftware
    Wednesday, January 26, 2011 10:04 AM
  • Mr. Bob,

    You are most welcome in this thread, and I certainly wish I could make good use of your well thought out advice. As it turns out, unfortunately, there are quite some bits and pieces still not included in the web application yet, such as the DatePart function.

    As mr. Kallal and others have pointed out in other posts I have found by searching, it is a matter of lighting a candle rather than cursing the dark. I have to adapt to the limitations, but it's not so easy to search for references and example code when almost everything out there is written in VBA ;)


    TEL
    Wednesday, January 26, 2011 10:48 AM
  • Hold on, I think I'm on to something here. I found a function called FormatDateTime in the expression builder. I tried to write a calculated test field in my query like this:

    Expr1: FormatDateTime(Date();[ww])

    When running the query, i get error message: Check the expression for errors or enter the parameter for [ww]..

    What am I doing wrong?


    TEL
    Wednesday, January 26, 2011 11:39 AM
  • Hi

    A search of VBA help file shows this

    FormatDateTime Function 

    Description

    Returns an expression formatted as a date or time.

    Syntax

    FormatDateTime(Date[,NamedFormat])

    The FormatDateTime function syntax has these parts:

    Part Description
    Date Required. Date expression to be formatted.
    NamedFormat Optional. Numeric value that indicates the date/time format used. If omitted, vbGeneralDate is used.

     

    Settings

    The NamedFormat argument has the following settings:

    Constant Value Description
    vbGeneralDate 0 Display a date and/or time. If there is a date part, display it as a short date. If there is a time part, display it as a long time. If present, both parts are displayed.
    vbLongDate 1 Display a date using the long date format specified in your computer's regional settings.
    vbShortDate 2 Display a date using the short date format specified in your computer's regional settings.
    vbLongTime 3 Display a time using the time format specified in your computer's regional settings.
    vbShortTime 4 Display a time using the 24-hour format (hh:mm).


    Bob Fitz BizSoftware
    • Edited by Bob Fitz Wednesday, January 26, 2011 11:48 AM Cut/paste didn't work as I expected
    Wednesday, January 26, 2011 11:46 AM
  • Hello.

    If the FotmatDateTime function is available, maybe the Format function is, too. Try
    Format([StartDate],"ww")

    --
    Kind regards
    Wolfgang

    Wednesday, January 26, 2011 12:18 PM
  • Wolfgang, I wish it wer so, but I get a message that the expression is not valid for web-compatible queries...:(

    However, I came up with the following expression for getting a calculated field [WeekNumber] in my Tasks query, based on the [StartDate] field:

    Expr1: Int((((Month([StartDato])-1)*31)+(Day([StartDato])))/7)

    It actually works :) Now if I only could find a way to populate a list box with the grouped week numbers, since the Group by-function is not included in web database!


    TEL
    Wednesday, January 26, 2011 1:21 PM
  • Hi Toby Honest

    I hate to deflate you but, (there's always a but)

    I've just tested your solution and while I'm not saying that it dosen't work I can tell you that it dosen't give the same results as the DatePart function gives.

    Some dates have a different week number, but at the momment I don't know why.

     


    Bob Fitz BizSoftware
    Wednesday, January 26, 2011 2:00 PM
  • Bob, it's OK. I know it's far from an accurate solution, I just need to have a demo functionality of the Filter-task-by-week-function till Friday's presentation :)

    I'm so happy I got you guys to ask, otherwise I'd be stuck in the mines of Moria :D


    TEL
    Wednesday, January 26, 2011 2:21 PM
  • Hi Toby Honest

    You wrote:

    It actually works :) Now if I only could find a way to populate a list box with the grouped week numbers, since the Group by-function is not included in web database!

    As I said before I know nothing of web DBs but if that means that you can't use Group By in a query then could you create a function that would return the values required, or can you not create user defined functions either.

     


    Bob Fitz BizSoftware
    Wednesday, January 26, 2011 3:56 PM
  • Bob, I think the answer is no, there is no way to create a user defined function... I'd happily stand corrected ;)
    TEL
    Wednesday, January 26, 2011 5:37 PM