Show the date of the next Monday?

Gwyar · #1 (**permalink**) 02-07-10, 14:43

Hi...
I am busy with lots of questions.
I have searched around for a bit of code that will do this. But can't seem to find any, Though there are some functions I would like to try. If only I knew how

Anyway...
I need to print a report. It needs to be finished by Monday morning.
It may be printed 1, 2 or 3 days ahead of time.
I can use the =Date()+1, or =Date()+3 depending what day it is actually printed etc, but the users won't have a clue how to do that, and I don't want them in there anyway.. ya know

and I have to make things as easy as possible for them..

Dooable?

Tracy

pbaldy · #2 (**permalink**) 02-07-10, 15:56

You should be able to adapt one of these to your needs:

Layout 1

Gwyar · #3 (**permalink**) 02-07-10, 16:38

Thank you

I'm just not sure what to do with these....

Tracy

Missinglinq · #4 (**permalink**) 02-07-10, 20:54

So you want the report dated the Monday following when it is actually run?

On your report, place an unbound textbox, then goto Properties - Data and in the Control Source Property place this code:

= (date + (7 - Weekday(date,vbMonday)) + 1)

myle · #5 (**permalink**) 02-07-10, 21:11

there a Fuction I use

Code:

Function WEEKEND(dat) As Date
If IsNull(dat) Then Exit Function
dat = DateSerial(Year(dat), Month(dat), Day(dat))
If dat Mod 7 > 0 Then
WEEKEND = dat - dat Mod 7 + 7
Else
WEEKEND = dat
End If
End Function

this will return the Sat date of any date pas to it

so if we add 2 to the both side of the if that sould be mon date ( untest)

Code:

Function WEEKENDMON(dat) As Date
If IsNull(dat) Then Exit Function
dat = DateSerial(Year(dat), Month(dat), Day(dat))
If dat Mod 7 > 0 Then
WEEKENDMON = dat - dat Mod 7 + 9
Else
WEEKENDMON = dat+2
End If
End Function

now to use it in your Qurey

monweek:WEEKENDMON([afeilddatevalue])

monweek will the monday date of [afeilddatevalue]

the in the Where Part

=WEEKENDMON(Now())

If my plan is right it should only show next mon data in the out of the query

Gwyar · #6 (**permalink**) 02-08-10, 00:27

Quote:

Originally Posted by Missinglinq

So you want the report dated the Monday following when it is actually run?

On your report, place an unbound textbox, then goto Properties - Data and in the Control Source Property place this code:

= (date + (7 - Weekday(date,vbMonday)) + 1)

Hi Missling
I was so hoping this was going to work.. But it wants me to 'Enter Parameter Values' of Date and vbMonday
Guess I cant even copy and paste

myle
I am trying to figure this out...
Where does this go?

Code:

Function WEEKENDMON(dat) As Date
If IsNull(dat) Then Exit Function
dat = DateSerial(Year(dat), Month(dat), Day(dat))
If dat Mod 7 > 0 Then
WEEKENDMON = dat - dat Mod 7 + 9
Else
WEEKENDMON = dat+2
End If
End Function

Where does this go?
monweek:WEEKENDMON([afeilddatevalue])

And lastly, you guessed it.. where does this go?
=WEEKENDMON(Now())

Sorry to make you repeat yourself but I'm having a hard time with this one.

Thank you very much
Tracy
================
I'm using 2007.
Also looking on you tube for video lessons on functions too

Ax238 · #7 (**permalink**) 02-08-10, 10:20

Quote:

Originally Posted by Gwyar

Hi Missling
I was so hoping this was going to work.. But it wants me to 'Enter Parameter Values' of Date and vbMonday

Try this:

Code:

=Date()+7-Weekday(Date(),2)+1

Ax

Gwyar · #8 (**permalink**) 02-08-10, 10:27

Thank You thank you....

Now it makes me wonder why I would want to use a function when one simple line of script will do the job?

Thanks again Ax

Tracy

Missinglinq · #9 (**permalink**) 02-08-10, 13:30

Actually, Ax's code is really identical to mine; he simply uses the 2, which is the day of the week for Monday, instead of the constant vbMonday, which evaluates to 2.

You stated that Access demanded a parameter value for vbMonday and Daye. This makes me wonder if you have one or more missing refences, as they are standard Access Constants/Functions. Exactly where were you trying to use this?

And yes, I agree, one line of code for something like this meakes more sense than calling a multi-line function. Some people are just more accustomed to doing everything with functions. It frequently just depends on the languages taht they learned coming up.

Glad you got it working!

Ax238 · #10 (**permalink**) 02-08-10, 13:41

Quote:

Originally Posted by Missinglinq

Actually, Ax's code is really identical to mine; he simply uses the 2, which is the day of the week for Monday, instead of the constant vbMonday, which evaluates to 2.

That, and I changed date to Date(), otherwise the parameter issue will still be present. As for missing references, I can't say, but you can't use vbMonday or date in a query.

Ax

Gwyar · #11 (**permalink**) 02-08-10, 16:56

Hello again..

Missinglinq:
I tried your code in an unbound textbox on a report.

Both codes look similar to me,

= (date + (7 - Weekday(date,vbMonday)) + 1)

=Date()+7-Weekday(Date(),2)+1

I don't know enough about them to know what I am looking for

But I am learning.. thanks to people like you

Tracy

Missinglinq · #12 (**permalink**) 02-08-10, 17:48

Quote:

Originally Posted by Ax238

I changed date to Date(), otherwise the parameter issue will still be present...you can't use vbMonday or date in a query.

Date works in VBA code without the parens, in v2003.

And you're correct, I believe, about not using the Constant vbMonday in a query, which is why I asked the OP about where he was using this. Since it was in a Report, this doesn't come into paly, and vbMonday should have worked just fine.

Ax238 · #13 (**permalink**) 02-08-10, 23:38

Thanks for clarifying

myle · #14 (**permalink**) 02-09-10, 21:43

Quote:

Originally Posted by Gwyar

Hi Missling
I was so hoping this was going to work.. But it wants me to 'Enter Parameter Values' of Date and vbMonday
Guess I cant even copy and paste

myle
I am trying to figure this out...
Where does this go?

Code:

Function WEEKENDMON(dat) As Date
If IsNull(dat) Then Exit Function
dat = DateSerial(Year(dat), Month(dat), Day(dat))
If dat Mod 7 > 0 Then
WEEKENDMON = dat - dat Mod 7 + 9
Else
WEEKENDMON = dat+2
End If
End Function

Where does this go?
monweek:WEEKENDMON([afeilddatevalue])

And lastly, you guessed it.. where does this go?
=WEEKENDMON(Now())

Sorry to make you repeat yourself but I'm having a hard time with this one.

Thank you very much
Tracy
================
I'm using 2007.
Also looking on you tube for video lessons on functions too

haven't played with 07 yet

try this

open the msaccess file and click on MODULES click New

then paste above code into the open window close it it should ask you to save it yes

then Open a Query window put a table in there drop some feild in

where you put the feild you should be able to type

monweek:WEEKENDMON([????])

???? = a feildname from the table that is a date value

run new query if working there be a new coloum call monweek
which should have mon's date in base on the ????

praying it working

then desgin the query again and in the Where part of the query
put below the monweek feild =WEEKENDMON(Now())

run again and you should only see next monday data in the output

Internet Services

Web Development

Digital Marketing

Consumer Tech