Date Field to be the next given Monday (1 Viewer)

[RussPhi]

Hi
I have a date field called (EndDate) that i can pick on the date picker "cumbersome" what I want to do on a "dble click" is to change the date to the monday of next week. So if today is Monday the 28th of Nov I want the date field to show next Monday the 5th of Dec. and if today is Nov 29th Tuesday I want it to show Monday the 5th of Dec. so no matter what the current day is this week I want the next week Monday. hope this makes sense.

Much tanks for any help.

 

[bob fitz]

The attached file has a number of Useful Date Functions. I'm sure it will have something of use to you.
I take NO CREDIT for its conception. Our thanks go to Pat Hartman for its existence.

 

[theDBguy]

Here's what I use:

DateAdd("d", 8-Weekday(Date(),2), Date())

Hope that helps...

 

[bob fitz]

I think the function you will need is:

Function fDateDayAfter(dtmDate As Date, bytDaySought As Byte) As Date
'   Function to return the date of the named day after the passed date
'   Accepts:dtmDate - any date
'   bytDaySought (e.g. vbMonday)
    If bytDaySought - WeekDay(dtmDate) >= 0 Then
        fDateDayAfter = dtmDate + bytDaySought - WeekDay(dtmDate)
    Else
        fDateDayAfter = dtmDate + bytDaySought - WeekDay(dtmDate) + 7
    End If
End Function

which can be found in the code module of the form called sfrmFirstWeekday

 

[RussPhi]

this is what I'm geting what am i doing wrong.

 

[bob fitz]

Try:
Me.EndDate = DateAdd("d", 8-Weekday(Date(),2), Date())

 

[Pat Hartman]

Thanks @bob fitz ,
Here's a link to the most recent version. You have a very old version. Ten years This was one of the first samples I ever made. I also added a change log to the newest version so you can see some of the changes I made in the past couple of years. If you have copies of my other sample db's you might want to see if there is a newer version in the sample-date-functions. I've updated almost all of the samples within the past two years Here's all the links

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

Word Automation Sample

This database presents an example of using VBA to fill bookmarked fields in a Word document. The Database is A2003 format. However, the only version of Word installed on my PC is W2007 so before using the example, you may need to open any code module and change the Word reference to whatever...

www.access-programmers.co.uk

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

Replacement Switchboard

This Switchboard form is based on the last VBA version which I think was A2k. I expanded it to allow for 12 items rather than 8 and you can easily change it to do more. I've included directions. This updated version has two switchboards. One more traditional and the other is big buttons...

www.access-programmers.co.uk

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

Chaining Forms

Many people open multiple forms at one time. This is especially prevalent when you are using the tab view which is the default. I find that to be confusing for users so I developed a simple technique that uses the OpenArgs to allow one form to open another in DIALOG mode and then hide itself...

www.access-programmers.co.uk

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

----------

Bad Data is Bad for Business - Pat & Tony - Video 1

Bad Data is Bad for Business - Pat & Tony - Video 1

screencast-o-matic.com

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[bob fitz]

Here's a link to the most recent version. You have a very old version. Ten years This was one of the first samples I ever made. I also added a change log to the newest version so you can see some of the changes I made in the past couple of years. If you have copies of my other sample db's you might want to see if there is a newer version in the sample-date-functions. I've updated almost all of the samples within the past two years Here's all the links

10 years. Where did they go? I hadn't realized I'd been a fan for so long. I've used several of your functions over the years and offered them to many posters here and on other forums. Thanks for the link Pat.

 

[Pat Hartman]

@RussPhi
theDBguy offered an alternate solution. I think it uses an undefined function which will be the next error you get after you take Bob's suggestion on how to fix the current error. Please look at Bob's code again. You are missing the first part of the expression which tells Access where you want the date to be placed.

If you want to bother to look at the example database, you might find the calculation easier to understand. I've excerpted it so you don't even have to take the time to look at the sample. Here is the code to use the function from my database.

Me.EndDate= fDateDayAfter(Date(), vbMonday)

If you look at the sample database, you will see that the two arguments in the sample come from fields on the form. Above, I hardcoded them so that they are always today and always monday which may be easier for you to understand but far less flexible although suitable for this situation..

This is the function from the sample database. Bob posted it already. I repeat it for convenience.

Function fDateDayAfter(dtmDate As Date, bytDaySought As Byte) As Date
'   Function to return the date of the named day after the passed date
'   Accepts:dtmDate - any date
'   bytDaySought (e.g. vbMonday)
    If Weekday(dtmDate) = bytDaySought Then
        fDateDayAfter = dtmDate + 7
    Else
        If bytDaySought - Weekday(dtmDate) >= 0 Then
            fDateDayAfter = dtmDate + bytDaySought - Weekday(dtmDate)
        Else
            fDateDayAfter = dtmDate + bytDaySought - Weekday(dtmDate) + 7
        End If
    End If
End Function

 

[RussPhi]

Try:
Me.EndDate = DateAdd("d", 8-Weekday(Date(),2), Date())

ya it works. you are awsome thanks a ton

 

[bob fitz]

ya it works. you are awsome thanks a ton

I'm glad you have a solution. Thank you for your kind words but TBH I think Pat and theDBguy are more deserving of any thanks.

 

[RussPhi]

I thank all of you for your wounder work and minds of knowledge as well.

 

[theDBguy]

I thank all of you for your wounder work and minds of knowledge as well.

Good luck with your project.

 

[gemma-the-husky]

I must say that when I have needed to do something like that, I tend not to try to find an elegant one-line solution and use something I can actually follow!

I would do more

nextdate=nextdate+1 'in case today actually is a monday
while day(nextdate)<>vbmonday
    nextdate=nextdate+1   
wend

 

[Pat Hartman]

@gemma-the-husky The loop is a more compact solution than the one I originally posted but I agree. Especially when dealing with novices, which most of the time we are, it is important to not be too clever with the code.