calculating years employees have worked for

[Ginty]

hi, i want to calculate how long each person has worked at a company.

i am using access 97.

would i be able to enter something in the criteria that would work this out for me?

something like year([START DATE]) - year(Now())
this does not work but i want to find out the number of years a person has worked for the company from the field START DATE .

thanks for you help.

 

[KenHigg]

datediff("Y",Date(),[Start_Date])

????


Edit: Sorry, try this:

DateDiff("yyyy",[start_date],Date())

 

[EMP]

NewField: DateDiff("yyyy",[Start Date],Date()) + (Format(Date(),"mmdd")<Format([Start Date],"mmdd"))


Put it in the Field row, not the Criteria row.
The expression will give you the number of complete years.

^

 

[Ginty]

the field row in the table ?

i tired to change the totals to expression in the query but i got the error "You tired to execute a query that doesn't include the specified expression 'LENGTH OF SERVICE' as part of an aggregate function"

Have you got any example databases of this expression working that i could view in access 97?

 

[Ginty]

I have got that working now...
so by changing the setting i can work out days, months, seconds etc...

LENGTH OF SERVICE: DateDiff("m",[START DATE],Date()) works out months

but would i able to work out the years, months and days?

 

[EMP]

I have got that working now...

LENGTH OF SERVICE: DateDiff("m",[START DATE],Date()) works out months

DateDiff() alone can't give the correct number e.g.

DateDiff("m", #12/31/2004#, #1/1/2005#) returns 1 (month)

DateDiff("yyyy", #12/31/2004#, #1/1/2005#) returns 1 (year)

.

but would i able to work out the years, months and days?

Take a look at this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=67472

^

 

[Ginty]

yeah i realised this when i used the code for working out date of births. That is what the rest of the code is for.

i cant seem to get this time elapsed function to work.

i entered this
Elapsed Time: getTimeElapsed([START DATE])

and got an error saying "undefined function 'getTimeElapsed' in expression"

it works in the example database that was attached but not in mine.

thanks for your help

 

[Ginty]

Is it because i have not writen the vb function in my database?

i dont have vb on this machine... would i need to install it?

or is there another way of getting the information i require?

 

[Ginty]

sorry for being such a pain, i created a module and added the code, it is working fine now.