Calculated Week ending Date

[lansel]

I have a form which was created from a table. In the form I have a Date field and a Weekending Date field. I would like to automatically calculate the weekending date from the date that is entered in the date field. Should I have created the form from a query? I have searched for an answer from the post but now I am confused. I am not very good with Access. Could you please help?

Thanks!

 

[pbaldy]

Generally, this type of thing

[DorDate]-Weekday([DorDate])+7

where DorDate is my date field, you'd replace with yours. It would violate normalization rules to store the week ending date. I just calculate it on the fly.

 

[lansel]

Thank you so much for taking the time to answer.

So are you saying I can't have the date field calculate the weekending field on the form? If I typed in what you suggested in the date field it would change it to the weekending date in the date field?

Thanks,

 

[bob fitz]

I think that what pbaldy is telling you is that you should NOT be saving the week ending in a table field because it is a calculation. Just calculate it when and wherever it is needed with the expression that was given. On a form, the expression would be used as the control source of an unbound text box.

 

[pbaldy]

Well, if you have 2010 you have the new calculated field, but I've never used it. Generally I would just use that formula to display the week ending date where I needed it. On a form, a textbox with a control source of:

=[DorDate]-Weekday([DorDate])+7

would work.

 

[lansel]

I went in and added the formula to the unbound text box and it worked but when I went to the next record I received an error message saying I must enter a value in the weekending date field. What did I do wrong?

Thanks!

 

[pbaldy]

I'm not sure; I tested and records with a blank date field simply displayed blanks in the week ending date textbox. Can you post the db here?

 

[lansel]

Sorry I didn't get back sooner. I have been away from my desk most of the day. I worked on the problem some more and I finally got it to work. I still don't know what I was doing wrong. But thank you very much for your help!

 

[pbaldy]

No problem!

 

[Triggsie69]

DATEADD("D", (8 - WEEKDAY(FinishedDateTime))MOD(7), FinishedDateTime) AS WeekEnding
This is what I had to do to allow my WeekEnding query field to stay within the same week (finishing on a Sunday) when the FinishedDateTime value was a Sunday. The other methods kept pushing the weekending to the next week (+7 days).

 

[pbaldy]

Welcome to the site. Is there a question, or did you not realize this thread was 7 years old?

 

[Triggsie69]

Hi pbaldy,
Thanks for the welcome. I did not notice that it was an old thread. It did present as a highly relevant thread when searching for a solution so I thought it would be good to post a solution that gets around the issue of pushing a Sunday to the next week.