default date to the following Monday

[Fisky]

Hi All,
I'm a first time user and just gettinting to grips with Access so here it goes.

I have a textbox in a form. What I want it to do is to display the following weeks Monday date. Sounds simple but requires you to know the Monday dates in advance

Fisky

 

[pr2-eugin]

Hello Fisky, Welcome to AWF

Copy the following code into a Standard module, The Save it with a name that is not getMonDate. The Compile the code. Use it as the Default value of the text box where you want to get eh Monday date.

Public Function getMonDate(Optional inputDate) As Date
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    If IsMissing(inputDate) Then inputDate = Date
    
    getMonDate = inputDate - Weekday(inputDate) + 9
End Function

 

[JHB]

You can use the Weekday function, the below code calculate the next Monday, where by me Monday is the first day of a week.

Date - Weekday(Date, vbMonday) + 8

 

[Fisky]

Thanks for the promp reply Paul. I've tried this, coped the code to Module1 and placed the '=Module1' in the text box Default Box but I get an error '#Name?'. Any suggestions?

 

[pr2-eugin]

No, copy the code into Module1, save it as Module1, compile the code. In the text box use =getMonDate()

 

[Fisky]

Thanks.Got a date '01/01/1900'?

 

[pr2-eugin]

Thanks.Got a date '01/01/1900'?

Who is that aimed at BTW? I get the right date.

 

[Fisky]

Sorry. Paul. closed the form and reopended it and it stops with and errror @ line 3 of the code: 'getMonDate = ...' . Am I missing a field in '(Optional inputDate)'?

 

[pr2-eugin]

What is the error? Error Number and Description.

 

[Fisky]

Hi Paul, Run-time error '13': type mismatch

 

[Fisky]

Hi Paul. Found this code on another thread. It gives the Monday of that week (starting Sunday). Can you modifiy this code?

=Date() - (DatePart("w", Date(), 2, 1)-1)

 

[namliam]

=Date() - (DatePart("w", Date(), 2, 1) - 1) + 7

Should return next monday

Or if you prefer:
=Date() - (DatePart("w", Date(), 2, 1)) + 6

 

[Fisky]

Thanks for that, it didn't work but found that :

=Date() - (DatePart("w", Date(), 4, 1)) + 6 works!

I'm using Access 2003 and just wounder if that has been the problem?

 

[namliam]

Made a too quick a short cut....

=Date() - (DatePart("w", Date(), 2, 1)) + 8

the 2 in this datepart work with monday as the start of the week (i.e. be day 1)
If you put 4 it makes Wednesday be start of the week (be day 1)

It kindoff doesnt really matter.