Link to home
Start Free TrialLog in
Avatar of Senniger1
Senniger1

asked on

Assign Beginning of Week as Sunday instead of Monday

I'm using Access 2003.

SELECT TimeCards.TimeID, TimeCards.EmpInit, IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [MiddleInit] & " " & [LastName]) AS [Full Name], TimeCards.DateWorked, ([DateWorked]-Weekday([DateWorked]))+2 AS BegWeek
FROM Employees INNER JOIN TimeCards ON Employees.Initials = TimeCards.EmpInit;

In this SQL statement, how can I assign the BegWeek to Sunday instead of Monday.

Thanks in Advance.
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

I take it from your question that your machines do not use the default of Sunday as first day of the week.
So use...

Weekday([DateWorked],1))
Avatar of Senniger1
Senniger1

ASKER

It does use Sunday as the first day of the week which is my problem because for the purposes of what I'm doing, I need Monday to be the first day of the week as our weeks run Monday through Sunday.

The problem is I have the following expression in Access.  

BegWeek: ([DateWorked]-Weekday([DateWorked]))+2

Everything works fine except when my user works on Sunday because the value of vbSunday = 1 and using the calculations below Sunday part of the next week.

Date                      BegWeek
11/5/2012      11/5/2012
11/6/2012      11/5/2012
11/7/2012      11/5/2012
11/8/2012      11/5/2012
11/9/2012      11/5/2012
11/11/2012      11/12/2012
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes that is true because my original question provided the SQL code.  Your response stating our machines weren't using Sunday as the beginning of the week was inaccurate so I tried to explain further what my need is.

Based on the information you provided, I was able to change my SQL code to the following and it works great - thanks!

SELECT TimeCards.TimeID, TimeCards.EmpInit, IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [MiddleInit] & " " & [LastName]) AS [Full Name], TimeCards.DateWorked, ([DateWorked]-Weekday([DateWorked],2)+1) AS BegWeek
FROM Employees INNER JOIN TimeCards ON Employees.Initials = TimeCards.EmpInit;

The expression now looks like the following:
BegWeek: ([DateWorked]-Weekday([DateWorked],2)+1)

Thank you for your help!