Getting Start of Week from week number

[Meltdown]

Hi everyone, I have a Date field in a query that is formatted on a weekly basis:
WeeklyDate: Format([Sent_Date],"ww")

I now want to get what the first Monday of the week was, given a week number from WeeklyDate. So if WeeklyDate = 1 then WeeklyStart = 1/3/2005, if WeeklyDate = 2 then WeeklyStart = 1/10/2005..and so on.

Thanks for any help

 

[shadow9449]

Hi, Melt.

I created this formula to find the Monday of the week of any given date. I created it for the weekly view of a scheduler I created:

first_day = DateAdd("d", -1 * (DatePart("w", MyDate) - 2), MyDate)

A bit of explanation is due (unless it's immediately obvious how it works ):

DatePart("w", MyDate) - returns the day of the week that MyDate falls on.

DateAdd("d", -1 *..., MyDate - tells it how many days to move back, compared to MyDate


If it's Monday (2), then we move back 0 days (-1 * (2-2)) = 0)
If it's Tuesday (3) then we move back 1 day (-1 * (3 - 2) = -1)

You can keep trying this formula, and you should find that it will always find the previous Monday.

I hope it helps you or someone else.

SHADOW

 

[Meltdown]

Thanks Shadow, I'm afraid your code won't work for me because I have to pass in a week number not a Date. eg 10, 21 40 or whatever.

I need to calulate the start of the week from a week number.

Thanks for the reply

 

[Rich]

I need to calulate the start of the week from a week number.

Thanks for the reply

Why if sent_date is in the query anyway?

 

[Meltdown]

Hi Rich, I'm doing a report that shows Receipts V Payments on a weekly basis, sometime thre are no Receipts for a certain week and sometimes there are no Payments, but I still need to show zero for those weeks.

To make sure I show every week thats in Receipts or Payments in the report, I formatted the Date field in each table on a weekly basis, then Unioned and Grouped the two results to give me all the weeks possible...so that's why I need to get the start of the week from a week number not Sent_Date.

Thanks

 

[Fizzio]

Hi Rich, I'm doing a report that shows Receipts V Payments on a weekly basis, sometime thre are no Receipts for a certain week and sometimes there are no Payments, but I still need to show zero for those weeks.

Is there any reason why you cannot have a LEFT JOIN in your query? If you are not sure what that is, post the SQL code and we'll adjust accordingly