Convert WeekNum to Sunday's date for that week

[spongie1]

This is my first post. Please tell me if I need to provide more information. I am still learning.

I have a table similar to this:

tblProduction
ProductionID
pDate
pItem
pQtyProduced

I want to sum the production by week, starting on a Sunday. I want to list Sunday's date instead of week number x.

I have cobbled together some "code" that seems to work, but I could really use some help improving it.

Here is what I have for my date field in my query:

Date Field: #12/29/02#+(DatePart("ww", [tblProduction].pDate,1/1/2003)-1)*7

I am mostly interested in getting rid of the #12/29/02# in favor of something more elegant.

I just don't know how to detect a given day of the week and get the date that corresponds to it.

I can read VBA, I would like to either learn a solution that could be written in a query or typed in as a VBA function. I still learning so I might follow-up any response with a few questions.

(I am familiar with how to group and sum table elements, so I did not include that information.)

Thank you in advance for your time,

Jason

 

[spongie1]

Okay, I figured it out. Sometimes I just need to ask the question before I can discover the answer sorry to waste your time guys.

tblProduction.[Date]-Weekday(tblProduction.[Date])+1

 

[spongie1]

In the above post, I did not follow my initial syntax. Here is something more similar in syntax to the structure of my original post.

Date Field: [tblProduction].pDate-Weekday([tblProduction].pDate)+1