Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2008
    Posts
    114

    Unanswered: Show the date of the next Monday?

    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

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You should be able to adapt one of these to your needs:

    Layout 1
    Paul

  3. #3
    Join Date
    Jan 2008
    Posts
    114
    Thank you
    I'm just not sure what to do with these....

    Tracy

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    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)
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    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
    Last edited by myle; 02-07-10 at 21:20.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Jan 2008
    Posts
    114
    Quote Originally Posted by Missinglinq View Post
    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

  7. #7
    Join Date
    May 2009
    Posts
    258
    Quote Originally Posted by Gwyar View Post
    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

  8. #8
    Join Date
    Jan 2008
    Posts
    114
    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

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    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!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    May 2009
    Posts
    258
    Quote Originally Posted by Missinglinq View Post
    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

  11. #11
    Join Date
    Jan 2008
    Posts
    114
    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

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Ax238 View Post
    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.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    May 2009
    Posts
    258
    Thanks for clarifying

  14. #14
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Quote Originally Posted by Gwyar View Post
    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
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •