Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-09-2012, 11:44 AM   #1
lansel
Newly Registered User
 
Join Date: Jul 2003
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
lansel
Calculated Week ending Date

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!

lansel is offline   Reply With Quote
Old 08-09-2012, 12:03 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,130
Thanks: 13
Thanked 4,079 Times in 4,014 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Calculated Week ending Date

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-09-2012, 12:34 PM   #3
lansel
Newly Registered User
 
Join Date: Jul 2003
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
lansel
Re: Calculated Week ending Date

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,

lansel is offline   Reply With Quote
Old 08-09-2012, 12:48 PM   #4
bob fitz
AWF VIP
 
Join Date: May 2011
Location: Essex England
Posts: 3,500
Thanks: 50
Thanked 679 Times in 662 Posts
bob fitz has a spectacular aura about bob fitz has a spectacular aura about bob fitz has a spectacular aura about
Re: Calculated Week ending Date

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.
__________________
Bob Fitzpatrick
Microsoft Community Contributor Award (2011)
If this helped, please click the scales at the bottom left of this posting.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Many thanks.


I have never failed. I have found a thousand ways that do not work!
bob fitz is offline   Reply With Quote
Old 08-09-2012, 12:51 PM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,130
Thanks: 13
Thanked 4,079 Times in 4,014 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Calculated Week ending Date

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-10-2012, 04:17 AM   #6
lansel
Newly Registered User
 
Join Date: Jul 2003
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
lansel
Re: Calculated Week ending Date

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!
lansel is offline   Reply With Quote
Old 08-10-2012, 07:07 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,130
Thanks: 13
Thanked 4,079 Times in 4,014 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Calculated Week ending Date

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?

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-10-2012, 12:35 PM   #8
lansel
Newly Registered User
 
Join Date: Jul 2003
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
lansel
Re: Calculated Week ending Date

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!
lansel is offline   Reply With Quote
Old 08-10-2012, 08:03 PM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,130
Thanks: 13
Thanked 4,079 Times in 4,014 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Calculated Week ending Date

No problem!
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-14-2019, 07:41 PM   #10
Triggsie69
Newly Registered User
 
Join Date: Jun 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Triggsie69 is on a distinguished road
Re: Calculated Week ending Date

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).
Triggsie69 is offline   Reply With Quote
Old 06-14-2019, 08:18 PM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,130
Thanks: 13
Thanked 4,079 Times in 4,014 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Calculated Week ending Date

Welcome to the site. Is there a question, or did you not realize this thread was 7 years old?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-16-2019, 02:14 PM   #12
Triggsie69
Newly Registered User
 
Join Date: Jun 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Triggsie69 is on a distinguished road
Re: Calculated Week ending Date

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.

Triggsie69 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Week-Ending Date brett429 Forms 14 04-10-2008 12:26 PM
Getting date of week ending jalldridge Reports 5 07-06-2005 04:21 AM
Week ending date IanT Queries 1 03-14-2002 03:51 AM
Week ending date IanT Queries 1 03-13-2002 08:13 AM
Getting the next Week Ending's date. Talismanic Modules & VBA 7 08-02-2001 08:32 AM




All times are GMT -8. The time now is 11:05 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World