Results 1 to 10 of 10
  1. #1
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451

    date to week number and back


    i have a combo box that list the date of each Monday that i want to convert to a week number. from that i would like to convert that date to a week number and then later be able to convert back to the original date for reports or form control source of a text box. I'm not having any luck at understanding what I'm doing wrong here. I'm using

    Code:
        Me.Text6 = (DatePart("ww", txtstartdate, vbMonday, vbFirstJan1)) 'gets week number of 4 from 1/18/2016
        Me.Text8 = DateAdd("ww", Text6, DateSerial(Year(Date), 1, 1)) 'converts back to 1/29/2016
    i could understand the issue of when the first week starts according to what format and maybe converting back with the wrong format but i'm getting over a week extra in my returns from text8 in my sample form. any ideas what i'm doing wrong or a better method of taking the date of a Monday, converting to week number and getting it back to the date of the Monday?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    put the code into the form
    usage:
    text6 = getWeekNo("8/1/12")
    text8 = getMonFromWeekNo(12)


    Code:
    Public Function getWeekNo(ByVal pvDate)
    Dim vDat
    
    vDat = getMon(pvDate)
    getWeekNo = DatePart("ww", vDat, vbMonday, vbFirstJan1)
    End Function
    
    Public Function getMonFromWeekNo(ByVal pvNum)
    Dim vDat
    vDat = DateAdd("ww", pvNum, DateSerial(Year(Date), 1, 1))
    getMonFromWeekNo = getMon(vDat)
    End Function
    
    
    Public Function getMon(ByVal pvDate) As String
    Dim vTarg, vDate, vD
    Dim i As Byte
    
     vTarg = vbMonday
     vD = pvDate
     i = Format(vD, "w")
     
     Select Case True
     Case i = vTarg
         vDate = pvDate
     
     Case i < vTarg
         vDate = DateAdd("d", 1, vD)
     
     Case Else
       vDate = DateAdd("d", vTarg - i, vD)
     End Select
    
    getMon = vDate
    End Function

  3. #3
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks ranman, very nice and clean. I'm getting my head around it and most is making sense except if i put 3/1/16 i get a week of 10 and then a return of 3/7/16. I'm gaining 7 days for each Monday return, I'm sure i can overcome this by having the getmonfromweekno function just subtract 1 from the returned but would like to see if i have something wrong first. as i step thru it the first time to get the week number the vdat value comes back correct as 2/29/16 and brings back a good week number but as it steps thru it again to get the date from getmonfromweekno its gaining a week at the case else for vdate. like i said i can overcome this but was wondering if this is something i did or not understanding.
    back to my original post, does anyone know what i was doing wrong?

  4. #4
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    anyone have any more insight? what was wrong with the first code and why i'm getting the additional week to my return with the module?

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    i don't understand why you want to back calculate the Monday date using the week number since you already have the Monday date.

    However.....

    The options for the last argument for the DatePart function are:
    Constant
    Value
    Description
    vbUseSystem 0 Use the NLS API setting
    vbFirstJan1 1 Start with week in which January 1 occurs (default)
    vbFirstFourDays 2 Start with the first week that has at least four days in the new year
    vbFirstFullWeek 3 Start with first full week of the year

    Looking at the calendar and you will see that the first "week" in Jan is only 2 days. I think you should change the last argument to "vbFirstFullWeek"
    If you use
    Code:
    DatePart("ww", #1/18/2016#, vbMonday, vbFirstFullWeek)
    the week number is 3.
    Using ranman's code 'getMonFromWeekNo()", the date returned will be #1/18/2016#.

  6. #6
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    Thanks Steve, currently for work scheduled the week of, i am storing the monday date and the friday date since this work can be completed anytime during the week. The thought was use an unbound date box displaying the monday to pick, store the week number value generated from the date box and then for reports convert back to monday and friday values.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Seems to me it is easier (and less prone to error) to store the Monday date and calculate the week number using a built in function rather than calculate and store a week number from a date, then have to try and recalculate the Monday date from the week number. But that is me.
    Hope you get it working....


    Does it make sense about the argument change?

  8. #8
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    Still looking at the week number, undecided if i like it or not. another problem i thought of was historical, what's to differentiate week 34 this year from week 34 next year, now i have to store a value for the year. I'm back to storing two values again. Yes, the argument change made sense.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't know your project, but if you store the date, you can always calculate the week number.... no need to store it.

  10. #10
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks, sometimes i don't know my own project as i keep learning i also keep seeing better potential.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-19-2015, 03:37 PM
  2. Replies: 3
    Last Post: 01-21-2015, 02:40 PM
  3. Replies: 5
    Last Post: 09-20-2012, 11:34 AM
  4. Week number - How do you reset?
    By BillG in forum Queries
    Replies: 11
    Last Post: 09-13-2012, 04:41 PM
  5. how to convert week number to date range
    By qwang1115 in forum Access
    Replies: 1
    Last Post: 02-13-2009, 11:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums