Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2009
    Posts
    15

    Unanswered: weekly pay id generated from current date

    Hi there
    I'm new to the database field and i am trying to setup a timesheet database for our company, the old one is very painful so i'm trying to update it.

    our timesheets run from wednesday to wednesday and for each week in the month we use the following code - Aug09-1 = week 1, Aug09-2 = week2 etc.
    i am trying to figure out a formula that will generate this automatically for me using the current date and would create a new code each week ready to be used. the figure would be stored in the database so it can be used in reports, billing and employee payments.
    basically payweeks are labelled accoring to the wednesday they start on.
    the week that runs from wed sept 30 to tues oct 6 this would be Sep09-5

    currently the guys in the workshop have to manually put enter this in and they often get it wrong.
    i hope i have supplied enough info.
    cheers
    Aaron

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If you have a hard and unbending rule that governs week one of your financial year, I have a function (based on the ISO standards) that will calculate the start date of a year. There are other functions that go with it to calculate week numbers and (if you use them and have similar rules governing them) period numbers. The start of year function is currently set to work from a Sunday, so a little tweaking would be required.

    (BTW, I'm sure you meant that your timesheets run from Wednesday to Tuesday, unless every Wednesday is counted in two weeks...)
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i thought you said (here: http://www.dbforums.com/new-members-...eekly-ids.html) that you were going to generate these week codes manually?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Without using complex coding, I would do the following:
    Create a table with fields for the start of each week, the finish of each week, and the week number e.g. 07/10/2009,13/10/2009, OCT09-1.

    Using the above table, use code to store each date and week number in another table e.g. 07/10/2009 OCT09-1, 08/10/2009 OCT09-1 etc. This could be done for the whole year or each month for the next month etc.

    You then would use DLOOKUP to lookup the week number based on the date entered.

    Using the above method would reduce the need to create complex coding and spend time testing for various conditions.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here:
    Code:
    Option Compare Database
    Option Explicit
    
    ' ISO/FDIS 8601:2000(E) ( ISO 2000  All rights reserved)
    ' =======================================================
    '
    ' 4.3.2.2 The calendar week
    ' -------------------------
    '
    ' In parallel with the reference system described in 4.3.2.1, there is a reference system
    ' based on an unbounded series of contiguous calendar weeks.
    '
    ' Each calendar week has seven calendar days as indicated in Table 2.
    '
    ' Table 2:  Calendar days
    ' -----------------------
    '
    ' +---------------+--------------+
    ' | Ordinal day   | Calendar day |
    ' | number in     | name         |
    ' | the week      |              |
    ' +---------------+--------------+
    ' |     01        |  Monday      |
    ' |     02        |  Tuesday     |
    ' |     03        |  Wednesday   |
    ' |     04        |  Thursday    |
    ' |     05        |  Friday      |
    ' |     06        |  Saturday    |
    ' |     07        |  Sunday      |
    ' +---------------+--------------+
    '
    ' The reference point of the week calendar assigns Saturday to 2000 January 1.
    '
    ' A calendar week is identified within a calendar year by the calendar week number.
    ' This is its ordinal position within the year, applying the rule that the first
    ' calendar week of a year is the one that includes the first Thursday of that year
    ' and that the last calendar week of a calendar year is the week immediately preceding
    ' the first calendar week of the next calendar year.
    '
    ' NOTE 1: These rules provide for a calendar year to have 52 or 53 calendar weeks.
    '
    ' NOTE 2: The first calendar week of a calendar year may include up to three days
    '         from the previous calendar year; the last calendar week of a calendar year
    '         may include up to three days from the following calendar year.
    '
    ' NOTE 3: The time-interval formed by the week dates of a calendar year is not the same
    '         as the time-interval formed by the calendar dates or ordinal dates for the same year.
    '         For instance: Sunday 1995 January 1 is the 7th day of the 52nd week of 1994, and
    '                       Tuesday 1996 December 31 is the 2nd day of the 1st week 1997.
    '
    ' NOTE 4: The rule for determining the first calendar week is equivalent with the rule
    '         the first calendar week is the week which includes January 4.
    
    Public Function ISO_WeekNumber(ByVal AnyDate As Variant) As Variant
    
    ' ISO 8601 calendar week number computation.
    '
        Dim Date_Thursday As Date           ' Date of Thursday in the week to be found.
        Dim Date_JanuaryFour As Date        ' Date of January 4 of the given date.
        Dim Date_FirstMonday As Date        ' Date of the first Monday in the week on January 4.
        Dim Index_JanuaryFour As Long       ' Number of the day for 4th January of the given date.
        Dim Index_AnyDate As Long           ' Index of the day for the given date.
        Dim Delta_Day As Long               ' Difference in days.
         
        If Not IsNull(AnyDate) Then
        
    ' 1. Compute the date for the Thursday in the week of the given date.
    '
            Index_AnyDate = WeekDay(AnyDate, vbMonday) - 1
            Delta_Day = 3 - Index_AnyDate
            Date_Thursday = DateAdd("d", Delta_Day, AnyDate)
    
    ' 2. Compute the date of January 4 for the same year as the Thursday computed in 1.
    '
            Date_JanuaryFour = DateSerial(Year(AnyDate), 1, 4)
            If AnyDate < Date_JanuaryFour Then Date_JanuaryFour = DateSerial(Year(AnyDate) - 1, 1, 4)
            Index_JanuaryFour = WeekDay(Date_JanuaryFour, vbMonday) - 1
        
    ' 3. Compute the date of the Monday in the same week as January 4 computed in 2.
    '
            Date_FirstMonday = DateAdd("d", -Index_JanuaryFour, Date_JanuaryFour)
    
    ' 4. Compute the difference in days between the dates computed in 2. and 3.
    '
            Delta_Day = DateDiff("d", Date_FirstMonday, Date_Thursday)
    
    ' 5. Divide by 7 with upper rounding.
    '
            ISO_WeekNumber = Delta_Day \ 7 + 1
        End If
    
    End Function
    Have a nice day!

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Here is a solution using code. Select a date from the calendar and then click on the Set Date button. The month and week will appear under the Week Reference label.
    Attached Files Attached Files

  7. #7
    Join Date
    Sep 2009
    Posts
    15
    cheers guys will give it a go,
    yes r937 i am entering it manually but the tech's are complaining about having to select the pay week asking why can't if work it out off the date.

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Here is my version I wrote on the Train to work

    Code:
    Function WEEKEND(dat)
    'Author StePhan Mckillen
    'Created 01/07/1999
    'Returns the Saturdays date of a date
    ret = 4
    If IsNull(dat) Then Exit Function
       dat = DateSerial(Year(dat), Month(dat), Day(dat))
    If (dat Mod 7) > ret Then
       WEEKEND = (dat - dat Mod 7 + 7) - (7 - ret) + 7
    Else
       WEEKEND = dat + (ret - (dat Mod 7))
    End If
    End Function
    
    Function Payweek(ThisDate)
    Dim MyDate As Date
    'what is the weekend date
    MyDate = WEEKEND(ThisDate)
    'what month year are we in
    MM = Month(MyDate)
    YY = Year(MyDate)
    'find the 1st weekend of the month
    st1 = weekend(DateSerial(YY, MM, 1))
    'whats the differance in weeks ad 1 because it 0 base
    wek = DateDiff("w", st1, MyDate) + 1
    'format the line and return the value
    Payweek = Format(st1, "MMM") & Format(st1, "YY") & "-" & wek
    End Function
    first we need to work out what wedday it is

    that what the weekend function does
    you should be able to put any date in the weekend and it will pass the wedday of that date in question

    Not fully tested yet.

    this should point you down the right trace

    just copy the above code into a module

    to use in a Query PAYWEEK:Payweek(feildname)

    in code
    me.feildname = PayWeek(date())
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Myle your code works for the week of the month.
    The requirement is that Wednesday is the first day of the working week so 7th October would be week 1 e.g. OCT09-1, but the 6th October would be in the fith week of September e.g. SEP09-5

  10. #10
    Join Date
    Sep 2009
    Posts
    15
    poppa smurf, thanks
    your code works great, now i just need to work out how to apply it to my timesheet system so that when i tech enters the date it generates the payweekid and puts in in the correct table.

  11. #11
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    The attached updated version maybe useful I created the code as function. When you open the form the current date and the Week reference is displayed. When you enter a date in the text box the week reference for the date entered is displayed.
    Attached Files Attached Files

  12. #12
    Join Date
    Sep 2009
    Posts
    15
    Poppa Smurf
    your code has been working AWESOMELY. Can't thank you enough now that the Technicians have stopped whinging. i used your first versious of of the code.
    thanks again
    Pricey

  13. #13
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I am glad that I was able to provide a solution to your problem and to keep your staff happy.

Posting Permissions

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