Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatic Dates in Access

    Hello,
    We've been using the attached (very stripped down) database to print timecard label every week. We just purchased a new time clock which does not indicate the current week's date. I would like to add a "from" date and a "to" date on the label. I'm not sure how to do it. I would also like it to be automatic so the person who does it doesn't have to "mess" with anything and get frustrated.

    Since we do these weekly, I would like the "from date" to always be the upcoming Sunday and the "to date" to always be the following Saturday. Can this be done easily? I really don't want to go the date stamp route.

    Thanks!
    Louise

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Dates in Access

    In playing around with the report, it looks like I can put a formula in the Control Source for the seperate text boxes. I tried the =Date() and it worked beautifully. Now I just need to figure out what formula to enter in there to get the future dates I need.

    Thanks
    Louise

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Automatic Dates in Access

    Below are examples of some user-defined functions you could use to calculate the beginning of week and end of week dates, relative to a specified date:

    <code>Private Const DAYS_IN_WEEK = 7</code>

    <code>Public Function GetFirstDayOfWeek(ByRef DateRef As Date, _</code>
    <code> Optional ByRef WeekOffset As Long = 0, _</code>
    <code> Optional ByRef FirstDayOfWeek As VbDayOfWeek = vbSunday) As Date</code>
    <code> On Error GoTo Err_Handler</code>
    <code> Dim strMsg As String</code>

    <code> ' Assumes Sun as default 1st day of week if FirstDayOfWeek not specified</code>
    <code> GetFirstDayOfWeek = DateRef - Weekday(DateRef, FirstDayOfWeek) + (DAYS_IN_WEEK * WeekOffset) + 1</code>

    <code>Exit_Proc:</code>
    <code> Exit Function</code>
    <code>Err_Handler:</code>
    <code> Select Case Err.Number</code>
    <code> Case 0</code>
    <code> Resume Next</code>
    <code> Case Else</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> MsgBox strMsg, vbExclamation, "GetFirstDayOfWeek Function - Unexpected Error"</code>
    <code> Resume Exit_Proc</code>
    <code> End Select</code>
    <code>End Function</code>

    <code>Public Function GetLastDayOfWeek(ByRef DateRef As Date, _</code>
    <code> Optional ByRef WeekOffset As Long = 0, _</code>
    <code> Optional ByRef FirstDayOfWeek As VbDayOfWeek = vbSunday) As Date</code>
    <code> On Error GoTo Err_Handler</code>
    <code> Dim strMsg As String</code>

    <code> ' Assumes Sun as default 1st day of week if FirstDayOfWeek not specified</code>
    <code> GetLastDayOfWeek = DateRef - Weekday(DateRef, FirstDayOfWeek) + DAYS_IN_WEEK * (WeekOffset + 1)</code>

    <code>Exit_Proc:</code>
    <code> Exit Function</code>
    <code>Err_Handler:</code>
    <code> Select Case Err.Number</code>
    <code> Case 0</code>
    <code> Resume Next</code>
    <code> Case Else</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> MsgBox strMsg, vbExclamation, "GetLastDayOfWeek Function - Unexpected Error"</code>
    <code> Resume Exit_Proc</code>
    <code> End Select</code>
    <code>End Function</code>

    Examples of use (current week):

    <code>? GetFirstDayOfWeek(Date())</code>
    <code>5/21/2006 </code>
    <code>? GetLastDayOfWeek(Date())</code>
    <code>5/27/2006</code>

    'To get next Sunday, relative to today:
    <code>? GetFirstDayOfWeek(Date(),1)</code>
    <code>5/28/2006 </code>
    ' To get next Saturday, relative to today:
    <code>? GetLastDayOfWeek(Date(),1)</code>
    <code>6/3/2006 </code>

    Optional parameters are used to make the functions more flexible, in case the "week" needs to be defined other than the VBA default, where Sunday is first day of week and Saturday the last day of week, and to allow "offsets". Example using optional parameters:

    <code>? GetFirstDayOfWeek(#1/1/2006#,-1,vbMonday)</code>
    <code>12/19/2005 </code>
    <code>? GetLastDayOfWeek(#1/1/2006#,-1,vbMonday)</code>
    <code>12/25/2005</code>

    The sample code can be copied into a standard code module and then used in your application where needed. Note: if using function in query expression, replace the "VbDayOfWeek " constant with its numerical value, ie, vbSunday = 1, vbMonday = 2, etc.

    HTH

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Dates in Access

    Holy smokes Mark! <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23> This must be something that goes into the Visual Basic somewhere? Will have to decipher all you gave me to see if I can find something that will work for me. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Thanks
    Louise

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Automatic Dates in Access

    PS - attached is copy of your sample database, I modified the report to illustrate how to use the functions in report textbox - see textbox ControlSource property:

    =GetFirstDayOfWeek(Date(),1)

    (I had to reformat slightly so date would fit in box.) Note - as is case with queries, you cannot used named VBA constants in ControlSource expression, you'd have to use the numerical equivalent. This doesn't apply in the case of your report because the week begins with Sunday, which is the default in VBA.

    HTH

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Dates in Access

    Mark,
    That looks great! Thank you! Now, I'll have to look behind the scene and see what you did and duplicate it. The original database is quite involved and hopefully I'll figure out how to incorporate this as well.

    Thanks again. Be prepared for more questions? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Louise

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Dates in Access

    Hi Mark,
    I was able to export your hard work into my real database without any problems! Also exported the report and everything went fine. Thank you so much!
    Louise

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Automatic Dates in Access

    Louise,

    Glad worked OK. One final note I neglected to mention earlier, if for some reason did not want to use functions, as alternative could use the following expressions as the Control Source in the textboxes on report to calculate the next Sunday and following Saturday. For "FROM" date:

    =Date()-Weekday(Date())+8

    For "TO" date:

    =Date()-Weekday(Date())+14

    This should provide same results. I like to use functions in some cases because more flexible, and if needed in more than one place in database you don't have to re-invent the wheel & figure out the formula again. But if not familiar with VBA code, using expressions like the examples above are a perfectly valid & simpler alternative.

    HTH

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Dates in Access

    This is great Mark! I will look into this alternative as well!

    Thanks again,
    Louise

Posting Permissions

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