Results 1 to 10 of 10

Thread: Access - Function to return the Date (Monday & Saturday date of current week

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2006
    Location
    India, Punjab, Bhatinda
    Posts
    1,689

    Question Access - Function to return the Date (Monday & Saturday date of current week

    Hi
    Iam in need of a function (VBA-Access) to return the date on Monday & Saturday of the current week.
    May somebody help...

  2. #2
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Access - Function to return the Date (Monday & Saturday date of current week

    Try this:
    Code:
    Function ThisMonday() As Date
        ThisMonday = Date + vbMonday - Weekday(Date, vbSunday)
    End Function
    Replace vbMonday with vbSaturday you will have ThisSaturday and similar for other days.
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  3. #3
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Access - Function to return the Date (Monday & Saturday date of current week

    Anhn its very nice, a simple adding with ur code
    Code:
    Enum Thisday
        Sunday = vbSunday
        Monday = vbMonday
        Tueday = vbTuesday
        Wednesday = vbWednesday
        Thuday = vbThursday
        Friday = vbFriday
        Satday = vbSaturday
    End Enum
    
    Function ThisWeekDay(MyDay As Thisday) As Date
        ThisWeekDay = Date + MyDay - Weekday(Date, vbUseSystem)
    End Function
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  4. #4
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Access - Function to return the Date (Monday & Saturday date of current week

    Quote Originally Posted by seenu_1st View Post
    Anhn its very nice, a simple adding with ur code
    What you added is incorrect in cases the system FirstDayOfWeek is not Sunday.
    You must always use vbSunday in Weekday(Date, vbSunday) for the function.

    Your Enum is not need, you can use vbDayOfWeek instead.
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  5. #5
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Access - Function to return the Date (Monday & Saturday date of current week

    thanks for the info.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  6. #6
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,593

    Re: Access - Function to return the Date (Monday & Saturday date of current week

    Quote Originally Posted by anhn View Post
    What you added is incorrect in cases the system FirstDayOfWeek is not Sunday.
    You must always use vbSunday in Weekday(Date, vbSunday) for the function.

    Your Enum is not need, you can use vbDayOfWeek instead.
    not only that but it looks to me that it is overkill to create the Enum based on 7 constants that already have those values. Just to get rid of the vb prefix?
    And I have a question. Are the 'abreviations' intentional or typos? Tueday, Thuday, Satday
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  7. #7
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Access - Function to return the Date (Monday & Saturday date of current week

    i just made it to make the function to use as a general function for all days, bt there is an easy solution vbDayOfWeek, and the abreviations is just for a sample thats all. anyhw thanks for the infos.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  8. #8
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Access - Function to return the Date (Monday & Saturday date of current week

    Might be being thick :/ have a look at the format function :
    Code:
    debug.print format(now, "dddd")
    Edit:

    Scratch that - must read the post! lol sorry

    What happens if the date is a sunday?

    Code:
    Public Sub FirstAndLastOfWeek(ByVal dte As Date, ByRef dteMonday As Date, ByRef dteSaturday As Date)
        Select Case Weekday(dte)
            Case 1
                 dteMonday=dte+1
            Case Else
                dteMonday = dte - (Weekday(dte) - vbMonday)
        End Select
        dteSaturday = dteMonday + 5
    End Sub
    
    Public Sub t()
    '---- testing sub...
        Dim dteS As Date, dteM As Date, dteN As Date
        
        FirstAndLastOfWeek Now, dteM, dteS
        Debug.Print dteM, dteS
    End Sub
    Last edited by Ecniv; Jun 23rd, 2011 at 10:20 AM.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2006
    Location
    India, Punjab, Bhatinda
    Posts
    1,689

    Re: Access - Function to return the Date (Monday & Saturday date of current week

    Thankz anhn
    could you tell me more about this portion..
    Code:
    Date + vbMonday
    also let me know if the below code is equally good or not..
    Code:
    Me.txtStartDate = Date - Weekday(Date, vbSunday) + 2
    BTW if I also need the saturday date, then...I tried adding vbSaturday instead of vbMonday is that correct....

  10. #10
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Access - Function to return the Date (Monday & Saturday date of current week

    VB Constants: vbSunday = 1, vbMonday = 2, ..., vbSaturday = 7
    So, you can use:
    Code:
    Me.txtStartDate = Date - Weekday(Date, 1) + 2 '-- Monday
    Me.txtEndDate = Date - Weekday(Date, 1) + 7 '-- Saturday
    But use vb...day is clearer.

    Also because the default of the 2nd parameter of Weekday() function is vbSunday=1, so you can omit it if you want: use Weekday(Date) instead of Weekday(Date, 1) or Weekday(Date, vbSunday).
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

Posting Permissions

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



Click Here to Expand Forum to Full Width