Calculated Week ending Date (1 Viewer)

lansel

Registered User.
Local time
Yesterday, 20:13
Joined
Jul 2, 2003
Messages
72
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

Wino Moderator
Staff member
Local time
Yesterday, 18:13
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Yesterday, 20:13
Joined
Jul 2, 2003
Messages
72
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

AWF VIP
Local time
Today, 01:13
Joined
May 23, 2011
Messages
4,717
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

Wino Moderator
Staff member
Local time
Yesterday, 18:13
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Yesterday, 20:13
Joined
Jul 2, 2003
Messages
72
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

Wino Moderator
Staff member
Local time
Yesterday, 18:13
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Yesterday, 20:13
Joined
Jul 2, 2003
Messages
72
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

Wino Moderator
Staff member
Local time
Yesterday, 18:13
Joined
Aug 30, 2003
Messages
36,118
No problem!
 

Triggsie69

New member
Local time
Today, 12:13
Joined
Jun 15, 2019
Messages
2
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

Wino Moderator
Staff member
Local time
Yesterday, 18:13
Joined
Aug 30, 2003
Messages
36,118
Welcome to the site. Is there a question, or did you not realize this thread was 7 years old?
 

Triggsie69

New member
Local time
Today, 12:13
Joined
Jun 15, 2019
Messages
2
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.
 

Users who are viewing this thread

Top Bottom