Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2019, 08:00 AM   #1
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
How to create a button that returns the following Monday of the current date.

Hi all,

I feel like this is a simple question with a direct answer I'm just not finding.

Can I make a button that when I click it, the dates in my database are all updated to the next Monday of the current date. Like today is Thursday the 15th, if I clicked the button the dates would be updated to Monday the 19th.

Thanks!

HangoutGuy is offline   Reply With Quote
Old 08-15-2019, 08:14 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,593
Thanks: 58
Thanked 1,444 Times in 1,425 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How to create a button that returns the following Monday of the current date.

Hi. To update multiple records, you can use an UPDATE query. But if you're doing frequent updates of all records to a date for next Monday, maybe you're not supposed to store that date at all. Just a thought...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-15-2019, 08:26 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to create a button that returns the following Monday of the current date.

Code:
Public Function FindNextDOW(dte As Date, whatDay As VbDayOfWeek) As Date
   FindNextDOW = dte + 7 - Weekday(dte + 7 - whatDay)
   If FindNextDOW = dte Then FindNextDOW = FindNextDOW + 7
End Function
debug.print FindNextDOW(Date, vbMonday)

result: 19-aug-2019

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
HangoutGuy (08-15-2019)
Old 08-15-2019, 09:12 AM   #4
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: How to create a button that returns the following Monday of the current date.

Thanks so much! I just would like some guidance implementing this. I have the button created and I have the code posted in the vba window. I don't know how to get the code to run without errors. All I need is to have the button update all the dates in one of my tables and I'm unsure how to.
HangoutGuy is offline   Reply With Quote
Old 08-15-2019, 09:13 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,600 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How to create a button that returns the following Monday of the current date.

I'm with theDBGuy. If you are updating ALL records to a specific date, there is something wrong with your logic. We can help with that if you tell us what you are trying to do.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-15-2019, 09:15 AM   #6
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: How to create a button that returns the following Monday of the current date.

Quote:
Originally Posted by Pat Hartman View Post
I'm with theDBGuy. If you are updating ALL records to a specific date, there is something wrong with your logic. We can help with that if you tell us what you are trying to do.
I have a table with projects with a start date and an end date. I have the ability to use a date picker to change the start date to whatever I want to be able to see how much time is left in a project. The start date will always be the same for every project. I just want a button that I can click that updates that start date to the following Monday.
HangoutGuy is offline   Reply With Quote
Old 08-15-2019, 09:20 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,593
Thanks: 58
Thanked 1,444 Times in 1,425 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How to create a button that returns the following Monday of the current date.

Quote:
Originally Posted by HangoutGuy View Post
I have a table with projects with a start date and an end date. I have the ability to use a date picker to change the start date to whatever I want to be able to see how much time is left in a project. The start date will always be the same for every project. I just want a button that I can click that updates that start date to the following Monday.
If the bolded part above is true, then one approach is to not store this information in the projects table. Perhaps it's better stored in a "settings" or "preferences" table instead.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-15-2019, 09:24 AM   #8
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: How to create a button that returns the following Monday of the current date.

Quote:
Originally Posted by theDBguy View Post
If the bolded part above is true, then one approach is to not store this information in the projects table. Perhaps it's better stored in a "settings" or "preferences" table instead.
Yes I realize there are far and away better approaches to what I am currently doing. This is my first access database that I will be building and I just need the simple work arounds to get done what I need done.
HangoutGuy is offline   Reply With Quote
Old 08-15-2019, 09:31 AM   #9
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,593
Thanks: 58
Thanked 1,444 Times in 1,425 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How to create a button that returns the following Monday of the current date.

Quote:
Originally Posted by HangoutGuy View Post
Yes I realize there are far and away better approaches to what I am currently doing. This is my first access database that I will be building and I just need the simple work arounds to get done what I need done.
Hi. No offense intended, but one of our responsibilities here, I think, is to help guide new database developers, such as yourself, avoid making mistakes and prevent pitfalls as much as possible. If you do some actions now towards that goal, the less likely you'll have to come back here every now and then asking for more workarounds. For example, we could give you a quick and simple code to do what you're asking to do (in fact, Arnel already gave you one), but it's just as simple to also create a separate table to store the project start date. That way, this particular part of the design is already fixed, and you won't have to deal with other problems you might encounter later on if you didn't fix it now.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 08-15-2019 at 10:56 AM.
theDBguy is online now   Reply With Quote
Old 08-15-2019, 10:50 AM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How to create a button that returns the following Monday of the current date.

put the function i gave in a Module.
on the click of your button, add code:
Code:
Private Sub buttonName_Click()
    Currentdb.Execute "Update yourTable Set StartDate = #" & Format(FindNextDOW(Date, 2), "mm/dd/yyyy") & "#"
End Sub

__________________
"Never stop learning, because life never stops teaching"
arnelgp 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
Create a Button to Duplicate Current Record PotatoPancakes Access Web 4 09-10-2015 06:03 AM
[SOLVED] [Solved] Limit Calendar to Current Monday thru Sunday burrina Forms 0 06-24-2015 07:58 PM
Question Need date of the 1st Monday of the current month ghudson General 5 05-24-2010 12:41 PM
Set default value to Monday of current week sponge General 2 08-21-2005 09:38 PM
[SOLVED] command button to create report for the current record igc Forms 4 04-24-2003 12:50 PM




All times are GMT -8. The time now is 08:18 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