Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2012
    Posts
    16

    Unanswered: Default To Next Thursday

    Is there any way of manipulating =now() to go to the next Thursday? This should all be happening in a text box, so a non-VBA solution would be appreciated. I do have a VBA method, but no way of converting that to a wwyy format and changing the textbox appropriately.

    So if I were to enter a new record on Monday 15th, it would jump to Thursday 18th but if I were to enter another record on Friday 19th, it would jump to Thursday 24th.
    Last edited by RedFredHunter; 02-10-13 at 23:49.

  2. #2
    Join Date
    Feb 2013
    Posts
    5
    Yes
    Create a function

    Pass in a date
    Return a date

    Use weekday to determine the input date
    See if it is <= Thursday 4 if it is
    Subtract thursday weekday 4 + input date weekday

    > Thursday

    Output date = input date + (7 (Sunday) - Input date weekday + weekday(Thursday) 4)



    Most computers are setup for Sunday = 7
    Monday = 1

  3. #3
    Join Date
    Dec 2012
    Posts
    16
    So...

    Private Sub JobName_Change()
    Dim nextThurs As Date
    Dim dayCounter As Integer
    dayCounter = 0
    nextThurs = Now()

    Do While Weekday(nextThurs) <> 5
    nextThurs = nextThurs + 1
    Loop

    MsgBox("Ding! " + cstr(format(nextThurs, wwyy)))

    End Sub

    Okay, tested that but the cStr(Format(nextThurs, wwyy)) is not converting to 713 as expected, but 14/02/2013 05:15:08pm

    I'm trying to change the content of a textbox to concat a string from a list (JobName) and add the week at the end so I would get, after changing JobName, something like Fred713
    Last edited by RedFredHunter; 02-11-13 at 00:18.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by RedFredHunter View Post

    ...So if I were to enter a new record on Monday 15th, it would jump to Thursday 18th but if I were to enter another record on Friday 19th, it would jump to Thursday 24th...
    First off, if Friday is the 19th, the next Thursday would be the 25th, not the 24th!

    Secondly, what do you want to happen to this Field if the Record is entered on a Thursday; default to that date or the following Thursday?

    Lastly, do you want to to happen based on the day the Record is created, or on a Date Value you're going to enter into the Record?

    Quote Originally Posted by RedFredHunter View Post

    ...This should all be happening in a text box, so a non-VBA solution would be appreciated. I do have a VBA method, but no way of converting that to a wwyy format and changing the textbox appropriately...
    I have no idea what any of this means; having this 'happen' in a Textbox certainly doesn't preclude it being done with VBA code, nor does formatting it. Formatting "wwyy" is going to give you the number of the week of the year (i.e. this is the 7th week of this year) and the year; is that what you really want?

    We really need a little clearer explanation of your exact needs, here, in order to help you.

    Linq ;0)>
    Last edited by Missinglinq; 02-11-13 at 00:19.
    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 2013
    Posts
    5
    Public Function KeyEnding(datDate As Date) As String
    Dim strOut As String
    strOut = ""
    If Weekday(datDate) <= 5 Then
    strOut = Format(datDate, "WWYY")
    Else
    strOut = Format(datDate + 3, "WWYY")
    End If
    KeyEnding = strOut
    End Function

    key = "Fred" & Keyending(#2/7/2013#)

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You have to enclose you formatting string in Double Quotes

    (Format(nextThurs, "wwyy")

    and since you didn't it is simply giving you next Thursday or 14/02/2013 05:15:08pm.

    Since you apparently don't need the time portion, you probably should replace Now() in your code with Date().

    Linq ;0)>

    Unless JobName is being selected from a Combobox, you should probably move the code from the JobName_Change event to the JobName_AfterUpdate event. The JobName_Change event will repeatedly fire with each character that is entered into it.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Feb 2013
    Posts
    5
    Private Sub JobName_Change()
    'Dim nextThurs As Date
    'Dim dayCounter As Integer
    'dayCounter = 0
    'nextThurs = Now()

    'Do While Weekday(nextThurs) <> 5
    'nextThurs = nextThurs + 1
    'Loop

    MsgBox("Ding! " & KeyEnding(Date())

    End Sub

    add in the function I gave you already..

  8. #8
    Join Date
    Dec 2012
    Posts
    16
    Thanks for the help guys, but after asking around my friends I got things sorted out without needing VBA.

    Solution:

    JobCode textbox has Control Source =[JobName]+CStr(Format(now(), "wwyy", 5))

    date() and Date doesn't seem to work so now() will have to do.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If Now() works then Date should work! But that aside, exactly how does

    Quote Originally Posted by RedFredHunter View Post
    =[JobName]+CStr(Format(now(), "wwyy", 5))
    Default to the next Thursday, which was the question you posed here?

    Linq ;0)>
    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
    Dec 2012
    Posts
    16
    Date seems to make the TextBox's Control Source think that there's a variable named [Date] floating around somewhere, causing an error.

    I wanted it to default to next Thursday so that it would give me the right week when I threw it into a WWYY format, since payday is on a Thursday and records entered on a Fri/Sat would have become stuck in a limbo that would have made it headache-y to access the records (since we're only getting weekdays).

    This solution sidesteps the real issue (not getting right week for pay).

  11. #11
    Join Date
    Feb 2013
    Posts
    5
    did you want a date or the next week.. I think that your not clear on the outcome your trying to do.

    is it a date.. for the next thursday after thursday's (friday-Sunday) and thursdays date from monday-thursday.
    or is it the week number and year.

    not sure at this point..

  12. #12
    Join Date
    Dec 2012
    Posts
    16
    I wanted a textbox to combine a FirstName value taken from a table of employees and then combine that with a now() function formatted to display the current week and year and then cast as a string (so it would make up a four digit number). These two would then be added together to make up a unique code string that indexes jobs.

    Everything has been solved, including the topic of this thread.

  13. #13
    Join Date
    Feb 2013
    Posts
    5
    if you use Fred0113
    you would only have one index number per week or is that your intention?

Posting Permissions

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