Calculate First Day of Any Week 3

[TheStriker]

I know this may be a stupid question but, I wanted to know if there is a calculation that will return the first day of the week in date format, regardless of the date. For example:

I have a field named 'dtCreatedOn' that are already populated with dates ranging from as far back as two years and I am trying to gather all dates within a specified week per the "Week of" date. The "Week Of" will be a calculated field, thus the beginning of a Date Range in a paramatized query. Also, I will need to repeat this for the Month and Year also.

I prefer working with the expression builder on this one, although SQL solutions are welcome as well.

Thanks In Advance
TheStriker

 

[KMKelly]

The calculation I use for beginning of week would be [DateField]-Weekday([DateField])+1. This assumes Sunday is the first day of the week. If the day of week for DateField was Wednesday it would be calc to 4, which counts Sunday as 1, so you need to add one so it will only subtract 3 to get to Sunday. If you want to use Monday as the first day of week, just add two.

Month and year would be easiest to do by using the Month() or Year() functions. There is a week one, but it has given me weird results when doing multiple years.

Kris

 

[scriverb]

Try this for a new column in your query:

CurrentDayOfWeek: DatePart("w",[YourTableName]![YourDate])

DateOfFirstDayOfWeek: DateAdd("d",DatePart("w",[YourTableName]![YourDate])*-1,[YourTableName]![YourDate])


Update this code with your table and date field names.

Post back with any questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Sorry I was one day off:

DateOfFirstDayOfWeek: DateAdd("d",(DatePart("w",[YourTableName]![YourDate])-1)*-1,[YourTableName]![YourDate])


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[TheStriker]

Thanks for your quick responses. This works great. Now all I need to do is figure out how to repeat this process for the Year and Month.

Have a star on me!! ;-)

Thanks
TheStriker