Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    1st/last day of week for date when week starts MON (2000)

    In the past I have used the following code to give me the first or last day of the week for a given date:
    <pre>Function FirstDayInWeek(Optional dteDate As Date = 0) As Date
    If dteDate = 0 Then
    dteDate = Date
    End If
    FirstDayInWeek = dteDate - Weekday(dteDate, vbUseSystemDayOfWeek) + 1
    End Function</pre>


    <pre>Function LastDayInWeek(Optional dteDate As Date = 0) As Date
    If dteDate = 0 Then
    dteDate = Date
    End If
    LastDayInWeek = dteDate - Weekday(dteDate, vbUseSystemDayOfWeek) + 7
    End Function</pre>



    My latest project is for someone who's payroll week runs Monday Through Sunday rather than Sunday through Saturday.

    I tried changing the code to add 2 or 8 instead of 1 or 7, but I get the wrong date when I use a Sunday.

    For example, if I try 1/16/2005, I shold get 1/10/2005 but I actually get 1/17/2005.

    Any ideas?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 1st/last day of week for date when week starts MON (2000)

    Replace vbUseSystemDayOfWeek with vbMonday. This tells VB to use Monday as the first day of the week.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1st/last day of week for date when week starts MON (2000)

    You really do make it look so easy.

    Can't believe I missed that. I even had to look up what vbUseSystemDayOfWeek did. Can't believe I never thought of using vbMonday...

    Thank you so much!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1st/last day of week for date when week starts MON (2000)

    Is there a site with definitions of all the commands? Like vbMonday or strPasswd, or even simple things like Dim and Sub....I'm trying to teach my subordinates, as well as learn something myself, and I just want something to reference....thanks!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 1st/last day of week for date when week starts MON (2000)

    A useful feature within the Visual Basic Editor is the Object Browser. You can activate it by selecting View | Object Browser, by clicking the Object Browser button on the toolbar or by pressing F2.
    Type a word in the search box, for example vbMonday, and press Enter.
    You will see a list of items that contain the search word, with more info below it.
    Click on any item in the list of found items, and press F1 to get online help.
    You can also click in any word in a module or in the Immediate window, and press F1 to get help.

    And you could buy a book on programming in Access, of course. If you do a search in this forum for beginning vba book or something like that, you'll find several posts with recommendations.

Posting Permissions

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