Question on using DateDiff in a query with more than one criteria

[conservativetoolshed]

Employee Hire & Termination Dates

Rick worked from 08/07/13 until 12/07/13
Jane worked from 02/03/14 until 09/03/14
Tom worked from 08/01/14 until 09/01/14

My report needs to reflect the number of weeks each employee worked between specific dates, 08/28/13 through 08/28/14.
I know that I can add DateDiff("ww",#08/28/13#,#08/28/14#) to my query to determine the number of weeks between my given dates but I don't how to apply that to each full period of employment.

Help is much appreciated

 

[ranman256]

Your query would ask for:
[emp], [Hire], [Term] , Weeks:DateDiff("ww", [Hire],[Term]) where [hire] >= 08/28/13 and [term] <=08/28/14,

 

[conservativetoolshed]

Thank you very much for your assistance. I'm not sure what I'm doing incorrectly, I keep getting a syntax error. Below is my expression, the "DOT" and "DOH" are the actual field names for the hire and term fields in my database. It highlights the "where" in my expression after the syntax error but I am having difficulty figuring out what to do with it.

Weeks:DateDiff("ww", [DOH],[DOT]) where [DOH] >= 08/28/13 and [DOT] <=08/28/14)