Working on Dates(Workweek) on Query

Posted on 2006-05-12
Medium Priority
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
  • 2
LVL 77

Expert Comment

ID: 16665540
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

peter57r earned 500 total points
ID: 16665674

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

GRayL earned 500 total points
ID: 16669270
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?

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

862 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