Filter Query

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

Hi All, first time here but I'm really stuck. Hope someone can help.
I'm using the access 2007 template for time and billing in which I have a
queried form from the project list for employee hours, Date Worked etc in
which I filter dates by using a command buttons attached to a query made by
using the advance filter sort. The first button filters & sorts the records
by current week & employee, the second similar but by current month. I would
like a third button to show the hours worked for the current fortnighly pay
period.
I have tried several ways using datepart, date diff functions but I am
relatively new to access and do not have the skills to do this. I have
manipulated the query "work conducted this week" to show from Wednesday to
Tuesday and have tried adding to it with little succsess, so here I am.
The first pay period for the year was Wednesday 9/1/2008 to Tuesday 22/1/2008.
Some of my field names are are [Date Worked] ,[Day], [Employee] [Start Time
End Time], [NT Hours], [OT Hours], [NB Hours].
I hope this is enouge information and thanks in advance
Regards
 
the query for it would be something like

select employee, sum([end time] - [start time])
from worktimetable
where [date worked] between [enter start of period] and dateadd([enter
start of period],-14,d)

when you run this query it will give you a prompt to enter a date
enter it in the format of you dates

ie my format is dd/mm/yyyy

hope this helps

Regards
Kelvan
 
Thanks for your reply Kelvan
With regards to your suggestion I dont need to calculate start time, end
time or employee as the times are already calculated on the split form and
the form has already been filtered for the employee in the query that the
form is based on. I thought of doing the between filter on the date worked
field but but it defeats the purpose of having a command button. I think the
answer maybe some form of DatePart of Date Diff function which relies on the
first date of the first pay period of the year and a variable between when I
filter the records ie. Date() or similar, and the start and end of the
current pay period.

Regards
 
Back
Top