#1
  1. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jan 2005
    Posts
    106
    Rep Power
    0

    Calculate Employment Years in query?


    I'm trying to calculate the amount of years employed in a query and I keep getting "Data Type Mismatch in criteria expression".

    I'm putting the expression in the YearsOfService Field that I've created that I would for the total in years to populate.

    Here's the field expression:
    DateDiff("yyyy",[EmpHireDate],Date())+(Date()<DateSerial(Year(Date()),Month([EmpHireDate]),Day([EmpHireDate])))

    Thanks for any help...

    FYI EmpHireDate is a Date/Time Field and YearsOfService is a Number field

    Thanks for any help...
  2. #2
  3. No Profile Picture
    Moderator: Access, SQL
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Posts
    5,126
    Rep Power
    19
    DateDiff("y",[EmpHireDate],DATE())

    You are not storing this value in the table are you?

    S-
    If you have found a particular post helpful, show your appreciation by adding reputation points to that user by clicking the "scales" image in the upper right had corner of their post.
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jan 2005
    Posts
    106
    Rep Power
    0

    umm...


    Originally Posted by sbaxter
    DateDiff("y",[EmpHireDate],DATE())

    You are not storing this value in the table are you?

    S-

    Yes I'd like to (is it a good idea?), I have a command button that I send that record to a former employee table that i'd like use that value also...also when i run, i get no entry's...empty.
  6. #4
  7. No Profile Picture
    Moderator: Access, SQL
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Posts
    5,126
    Rep Power
    19
    You should never store cacluated values (very rarely). Just caclulate them on the fly

    S-
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jan 2005
    Posts
    106
    Rep Power
    0

    makes sense...


    Originally Posted by sbaxter
    You should never store cacluated values (very rarely). Just caclulate them on the fly

    S-
    That makes sense, well the expression you gave me intially is still not working i get null values. I do put the expression in YearsOfService field, correct?

    S- thanks for your help and patience i'm new to this field (Access/VB) my background is Networking and i've been tasked for this project for some odd reason. BUDGET REASONS!
  10. #6
  11. gears always turning....
    ASP Adventurer (500 - 999 posts)

    Join Date
    Dec 2004
    Location
    St. Louis, MO, USA
    Posts
    709
    Rep Power
    121

    Post


    eddiemm3,


    Your original expression should be fine. I have dropped it into a query I built to work up a solution for you, and it worked perfectly as you provided it. The original "yyyy" is correct, that is the proper argument for year. The "y" will provide you the day of the year.

    As sbaxter stated, if you are trying to store the value, it may cause you problems, but in a query it works just fine. If, for whatever reason you do try to store the results, be aware that the field's data type needs to be a Long Integer.
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jan 2005
    Posts
    106
    Rep Power
    0

    Talking good 2 go


    Originally Posted by bubbablaze
    eddiemm3,


    Your original expression should be fine. I have dropped it into a query I built to work up a solution for you, and it worked perfectly as you provided it. The original "yyyy" is correct, that is the proper argument for year. The "y" will provide you the day of the year.

    As sbaxter stated, if you are trying to store the value, it may cause you problems, but in a query it works just fine. If, for whatever reason you do try to store the results, be aware that the field's data type needs to be a Long Integer.
    It took me a LOOONNNGG minute to realize what i was doing wrong, but i got it to work like sbaxter stated. but i used my regular expression Years Of Service: DateDiff("yyyy",[EmpHireDate],Date()) .... thank you...
  14. #8
  15. No Profile Picture
    Moderator: Access, SQL
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Posts
    5,126
    Rep Power
    19
    Originally Posted by bubbablaze
    The original "yyyy" is correct, that is the proper argument for year. The "y" will provide you the day of the year.
    Duh!
    Be a Long day. I should really try to get more sleep. Then I might actually be awake when I help people

    S-
  16. #9
  17. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jan 2005
    Posts
    106
    Rep Power
    0

    Not returning months...


    Originally Posted by eddiemm3
    It took me a LOOONNNGG minute to realize what i was doing wrong, but i got it to work like sbaxter stated. but i used my regular expression Years Of Service: DateDiff("yyyy",[EmpHireDate],Date()) .... thank you...
    this expression is not returning months. does anyone know how i can correct this?

    This is the expression...

    Years Of Service: DateDiff("yyyy",[FempHireDate],[FempRetDate])+([FempRetDate]<DateSerial(Year([FempRetDate]),Month([FempHireDate]),Day([FempHireDate])))
  18. #10
  19. gears always turning....
    ASP Adventurer (500 - 999 posts)

    Join Date
    Dec 2004
    Location
    St. Louis, MO, USA
    Posts
    709
    Rep Power
    121

    Post


    eddie,

    Visit the thread located here:
    http://forums.aspfree.com/t47235/s.html
    John
  20. #11
  21. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jan 2005
    Posts
    106
    Rep Power
    0
    Originally Posted by bubbablaze
    eddie,

    Visit the thread located here:
    http://forums.aspfree.com/t47235/s.html
    John
    Thanks john...But how would I be able to modify it to a retired date and a hire date?
  22. #12
  23. gears always turning....
    ASP Adventurer (500 - 999 posts)

    Join Date
    Dec 2004
    Location
    St. Louis, MO, USA
    Posts
    709
    Rep Power
    121

    Post


    This should do the trick:

    Years Of Service: DateDiff("yyyy",[FempHireDate],[FempRetDate])+([FempRetDate]<DateSerial(Year([FempRetDate]),Month([FempHireDate]),Day([FempHireDate]))) & " Years, " & (DateDiff("m",[FempHireDate],[FempRetDate])) Mod 12 & " Months"
  24. #13
  25. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jan 2005
    Posts
    106
    Rep Power
    0

    Thanks bubba...


    Originally Posted by bubbablaze
    This should do the trick:

    Years Of Service: DateDiff("yyyy",[FempHireDate],[FempRetDate])+([FempRetDate]<DateSerial(Year([FempRetDate]),Month([FempHireDate]),Day([FempHireDate]))) & " Years, " & (DateDiff("m",[FempHireDate],[FempRetDate])) Mod 12 & " Months"
    Disregard....Thanks...

Similar Threads

  1. Auto Numbering
    By woodstockjs in forum Microsoft Access Help
    Replies: 2
    Last Post: September 28th, 2004, 11:19 AM
  2. Crosstable query returns too many records
    By Palsam in forum ASP Development
    Replies: 7
    Last Post: March 12th, 2004, 08:07 AM
  3. Crosstable query returns too many records
    By Palsam in forum Microsoft Access Help
    Replies: 4
    Last Post: March 12th, 2004, 07:56 AM
  4. Query to calculate total of four tables
    By kabir_hussein in forum Microsoft Access Help
    Replies: 1
    Last Post: March 5th, 2004, 10:39 AM
  5. Replies: 2
    Last Post: December 19th, 2003, 10:02 AM

IMN logo majestic logo threadwatch logo seochat tools logo