Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2012
    Location
    North Carolina, USA
    Posts
    5

    Thumbs up Unanswered: Employee Sick Vacation db

    I need some major help on a database I am working on. It is someone else's work and I need to have additional revisions which are beyond my level of expertise (still new at this). It needs to be able to track employee hire dates and provide 9 month increments for employee evaluations. If the eval does not happen in nine months, it needs to flag that employee and still needs to work off of the original hire date for evals. There needs to be report monthly for my manager to show who is up for eval for that particular month. If someone could check this out and finish it up for me I would greatly appreciate it. I really need to get it completed and am at a standstill.

    Thank you.
    Attached Files Attached Files
    Last edited by userman; 01-08-13 at 05:09.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Here this mite help
    He is a function the return the year.months
    Code:
    Function Age(Birth_Date,End_Date)
    '***************************************
    ' Works out the age of to the month
    '
    '***************************************
    Dim Months
    Dim Years
    Dim Temp
    If IsNull(Birth_Date) or Birth_Date ="" Then
        Age=0.0 
    else
        Months = DateDiff("m", CDate(Birth_Date), End_Date)
        Years = Int(Months / 12)
        Temp = Years * 12
        If Years = 0 then Years = "" 
        Age =  Years & "." & Months - Temp
    End if
    
    End Function
    Just code code into module save

    Then pass to date to it

    In query here:age(feildstartdate,date()) will return year.months


    [\code]
    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.

  3. #3
    Join Date
    Nov 2012
    Location
    North Carolina, USA
    Posts
    5
    Thanks for the code for DOB and age calculations.

    What I am seeking now is to set up an DOH (date of hire) and add nine months to it every nine months. This is for employee evaluations which may or may not occur on the ninth month. If not the employee name needs to be flagged for attention.

    There needs to be a yes/no field for the evaluation happening/not happening. Once employee eval happening occurs then the flag goes away and nine months are added to the original DOH.

    This needs to calculate for employees who have ongoing tenure of several years.

    In other words, the nine months is alway calculated off of the original hire date and not the date of evaluation.

    Does this make sense?

    Thank you for your help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The functions usually used to perform arithmetics on date values are DateAdd to add or subtract a given scalar value from a date, and DateDiff to retrieve the scalar difference between to date values.

    So, in your case:
    Code:
    NewDate = DateAdd("m", 9, DOH)
    Have a nice day!

  5. #5
    Join Date
    Nov 2012
    Location
    North Carolina, USA
    Posts
    5
    Thanks Sinndho. I'll give it a try and see what I can come up with. I am assuming this goes into a new fileld as an experssion.

    DateOfEval = DateAdd("m", 9, DOH)

    Correct?

    When I enter the code DateOfEval = DateAdd("m", 9, DOH) and try to run in query I get a paramenr box requesting DOH. When I enter 01/23/95 and try to run I get the following:

    Expr1: [DateOfEval]=DateAdd("m",9,[DOH]) but not data in the NextEval field
    Last edited by userman; 01-10-13 at 07:11.

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Try this

    DateOfEval: DateAdd("m", 9, DOH) or replace the = with the :

    In the query

    In the where bit set the flag to no or yes (depending on how you want to store the flag)

    Then it will only should the one that have the flag not set and the dateofeval
    Last edited by myle; 01-10-13 at 09:35. Reason: Spelling
    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.

  7. #7
    Join Date
    Nov 2012
    Location
    North Carolina, USA
    Posts
    5

    Question

    I've tried the code and when I put in a DOH of 09/19/1993 I get an eval date of May 2994. What is wrong with this?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If DOH is the name of a column in a table or in another query, the syntax in the query editor should be:
    Code:
    DateOfEval: DateAdd("m";9;[DOH])
    Note: Depending on the international settings of Windows, you could have to use a comma ( , ) instead of a semicolon ( ; ). That would give:
    Code:
    DateOfEval: DateAdd("m",9,[DOH])
    In any case, when you switch to SQL view, you'll find:
    Code:
    SELECT DateAdd("m",9,[DOH]) AS DateOfEval
    FROM SomeTable;
    If the contents of the column DOH of SomeTable is 09/19/1993 (in mm/dd/yyyy format), the query will yield 06/19/1994 (in mm/dd/yyyy format).

    However, if DOH is a litteral value, it must be enclosed in a pair of Pound (or Number) sign ( # ): #09/19/1993#.
    Have a nice day!

  9. #9
    Join Date
    Nov 2012
    Location
    North Carolina, USA
    Posts
    5
    Thanks for the tip Sinddho - I've tried it with the ; and get an syntax error message. I've used the # and still get the same results. What do I do now? Since 1994 is past, I need a result that reflects the 2013 DateOfEval. I have several employees whose evals come up this year and that is what the query should reflect. It needs to calculate from the DOH to the future DateOfEval, not just 9 months from the DOH but all the intervening years as well.

    The code works great for those whose DOH was 2012, but I need to reach back further and calculate that to 2013 and beyond as evals will come again next year and some even in this year.
    Last edited by userman; 01-11-13 at 07:07.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you post the SQL expression of the query as you have it?
    Have a nice day!

  11. #11
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Dum question is the DOH a date feild in the table. ???
    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.

Posting Permissions

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