Sum hours between anniv date and end date

[rworthy]

Hi I am trying to create a query that takes the current anniversary date and the end date that I enter and calculate the hours worked between those two dates.



So I have a column with everyones anniversary date and I have a column of hours. When I call up the query I want it to ask the end date and then calculate.

Thanks.

[June7]

Sounds like you need an aggregate (Totals Group By) query. Access Help has guidelines on creating queries.

[MSAccessCode.com]

Can you please provide some sample data to better understand how you want this calculated?

[rworthy]

Can you please provide some sample data to better understand how you want this calculated?

So this is my query.

Employee Number	LAST NAME	FIRST NAME	ANNIV DATE	Sum Of Final Hours
30	Fake name 1	Peter	10/07/12	5369
40	Fake name 2	Mark	01/28/13	5149.5
200	Fake name 3	Gordon	08/27/12	3966.5
300	Fake name 4	George	04/03/13	2307.25

So I want to create a report that picks up the hours worked from the Anniv Date to whatever end date I enter when opening the report. So say i want to run the report on May 18, 2013 it should take the hours worked from Fake Name 1 Oct 7, 2012 to May 18, 2013.

[June7]

In other words, you want all the records where the AnnivDate is before the criteria date? Criteria under AnnivDate field simply: <=[enter date].

However, I never use input popups to enter parameters. Use unbound textbox on form to enter value and reference textbox as parameter: <= Form!formname!textboxname

Also, instead of dynamic parameters in query, I prefer to pass criteria to report with code:

DoCmd.OpenReport "report name", , , "AnnivDate=#" & Me.textboxname & "#"

Is your report designed using Grouping & Sorting features with aggregate calcs in header/footer sections? This will allow display of detail records as well as summary calcs.

[rworthy]

In other words, you want all the records where the AnnivDate is before the criteria date? Criteria under AnnivDate field simply: <=[enter date].

However, I never use input popups to enter parameters. Use unbound textbox on form to enter value and reference textbox as parameter: <= Form!formname!textboxname

Also, instead of dynamic parameters in query, I prefer to pass criteria to report with code:

DoCmd.OpenReport "report name", , , "AnnivDate=#" & Me.textboxname & "#"

Is your report designed using Grouping & Sorting features with aggregate calcs in header/footer sections? This will allow display of detail records as well as summary calcs.

So my anniversary date is the date within the last year but they started working Say 1998. So I only want it to pick up the hours worked since the anniversary date.

[June7]

How tight do you want this criteria, up to the current day or current week or current month?

For current month, create field in query that extracts month from AnnivDate: Month([AnnivDate]). Criteria: <=Month(Date()) (or require user input for the month criteria).

For a specific date, calculate the current year anniversary: DateSerial(Year(Date()), Month([AnnivDate]), Day([AnnivDate])). Now apply date criteria to that constructed field. If their anniversary Feb 29 and current year is not a leap year, the calc will return 3/1/year. However, this prompts question of how far back should the anniversary date be calculated. If the current date is January 3, do you want the anniversary date for the previous year? Consider:

DateSerial(Year(Date())-IIf(DateSerial(Year(Date()), Month([AnnivDate]), Day([AnnivDate]))>Date(),1,0), Month([AnnivDate]), Day([AnnivDate]))