Get Start date of Week number and Year

[h2fcell]

I’d like to build the following expression in my query
GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
So if EnteredDate = 11/3/2009 the function would return 11/1/2009

But GetStartWeekNumber does not exist as an Access Built-In Function.
Is there another way to do this as an expression in a query?
I’m not familiar with creating my own functions.

Thanks.

 

[Jeff Boyce]

That would depend on how you define the start of the week...

One option would be to get the day-of-week number of the date (in my
system/setup, Monday is day 2), then subtract one less than that from the
date to get the date on the start of THAT week.

Yes, you can do that in a query expression.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jim Bunton]

I think this will help:
Weekday(date, [firstdayofweek])
Returns a Variant (Integer) containing a whole number representing the day
of the week.
To get Sunday to be the first day of the week enter 1 for firstdayofweek OR
the constant vbSunday

for the date 3rd nov 2009 this returns 3 So you know Sundays 3 days back
from the date you entered

so then use dateAdd to get the date three days back and return it from the
function

Jim

 

[Daryl S]

H2fcell -

Try this:

=DateAdd("d",-DatePart("w",[EnteredDate])+1,[EnteredDate])

This will check the day of the week (1 = Sunday) and subtract the number of
days less one from the EnteredDate. So if the current day of the week is
Tuesday (DatePart will return 3), we subtract 2 days from the entered date.

 

[Jerry Whittle]

FirstDayWeek: [EnteredDate] - Weekday([EnteredDate]) +1

The above assumes that you want Sunday to be the first day of the week.

 

[KARL DEWEY]

Try this --
DateAdd("d",-Weekday([EnteredDate])+1,[EnteredDate])

 

[Jerry Whittle]

Seems that my first reply didn't make it.

FirstDayOfWeek: [EnteredDate] - Weekday([EnteredDate]) +1

The above will work in a query. It assumes that Sunday is the first day of
the week.

 

[Andy Smith]

Try this:

[Entered Date] + 1 - Weekday([Entered Date])

Weekday returns 1 for Sunday up to 7 for Saturday, and when you add or
subtract integers to and from dates, the integers count as days forward or
backward.

 

[Wayne-I-M]

DateAdd("d",-(Weekday([Some Date])-1),[Some Date])

will give the previous sunday of any date

 

[Daniel Pineault]

Here is a procedure that will calculate the first day of the week for the
given date. Copy and paste it into a module and then you can call it in your
forms, report, queries.

'---------------------------------------------------------------------------------------
' Procedure : GetFirstofWeek
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine the date of the first day of the week for a given date
' Copyright : The following may be altered and reused as you wish so long as
the
' copyright notice is left unchanged (including Author, Website
and
' Copyright). It may not be sold/resold or reposted on other
sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' dtDate : Date to find the start of the week of
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' GetFirstofWeek(#10/2/2009#)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2009-Nov-16 Initial Releas
'---------------------------------------------------------------------------------------
Function GetFirstofWeek(dtDate As Date)
On Error GoTo Error_Handler
'GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate)) + 1) 'Returns
the Sunday
GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate)) + 2) 'Returns
the Monday

Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GetFirstofWeek" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[h2fcell]

All I can say is WOW! This date math is simple yet elegant.
Thank you very much. This is what I was looking for.

Try this:

[Entered Date] + 1 - Weekday([Entered Date])

Weekday returns 1 for Sunday up to 7 for Saturday, and when you add or
subtract integers to and from dates, the integers count as days forward or
backward.

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard & Poor''s, NYC

I’d like to build the following expression in my query
GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
So if EnteredDate = 11/3/2009 the function would return 11/1/2009

But GetStartWeekNumber does not exist as an Access Built-In Function.
Is there another way to do this as an expression in a query?
I’m not familiar with creating my own functions.

Thanks.