Working on Dates(Workweek) on Query

Posted on 2006-05-12
Last Modified: 2010-08-05
Hello Experts:

i have uploaded here:

my excel sample just to show you what i want.

I want to create a formula in in the design view query same as the column Workweek in excel given. I have the Transaction date in access but i want to add a column that determines the Workweek# on a specified range of dates. Workweek starts on Mondays. Although the Days column are NOT present in the access table column. I jsut shown it in excel just to show where the workweek starts
Question by:newton_aquino
    LVL 77

    Expert Comment

    Hi newton_aquino,

    There is no simple formula to do what you want.(in Access or XL)
    There IS a simple formula which will deal with dates in that belong to the same company year as their calendar year but this formula fails at the beginning and end of the company year.

    To do what you want you would need a table showing the Company year and the start date of the company year for each year covered by your data.
    You could then have a function which for any given date, found the relevant start date for the year it belongs to and calculates the week number.

    LVL 77

    Accepted Solution


    Assuming you set up the table as described, here are the functions you need.
    Paste them into a standard module.

    Function GetWkNo(mydate As Date) As String
    Dim StartDate
    Dim wk As Long
    StartDate = GetStart(mydate) ' get the start date for the correct year

    wk = ((mydate - StartDate) + 7) \ 7  ' calculate the week no in that year

    GetWkNo = "WW" & Format(wk, "00")
    End Function

    Function GetStart(mydate As Date) As Date

    ' requires a table called CoYrs containing 2 columns - CoYear (long) and YrStart (datetime)
    ' Example 2005 27-dec-2004

    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select top 1 coyrs.* from coyrs where yrstart<= #" & mydate & "# order by yrstart desc")
    If Not rs.EOF Then
        GetStart = rs!yrstart
        GetStart = #1/1/1900#   ' for dates with no corresponding year
    End If
    Set rs = Nothing
    Set db = Nothing

    End Function

    In a query you just do :


    LVL 44

    Assisted Solution

    Select TransactionDate, DatePart("ww",TransactionDate) from myDates;

    This will give you the weeknumber.  However, the dates from 2005-12-26 to 2005-12-31 are in fact week 53 of 2005 just like 2006-12-31 will compute as week 53 of 2006, not week 1 of 2007.  Can you live with that?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now