Question about dates

[Gmoz]

I know this subject has been covered pretty extensively but my search has not uncovered the info I am looking for.
I have a StartDate, an EndDate, and a Status per record what I need is to be able to pull a report by month for each status.

March Report
Status 1 3hrs 2min
Status 2 6hrs 9min

My problem is that my Start and End dates may span multiple months like start March 5th 12:00 and end April 12 09:10 When this happens I need the March portion in March and the april portion in April

Any help is greatly appreciated

Thanks, Frank

 

[Bat17]

filter your data by date field to limit the data set to just the data you want before grouping by month/status

Peter

 

[Gmoz]

I am not sure what you mean by "filter your data by date field ". Which one start or end?

I do not see where either would help. If I have a status that starts Mar 2nd 12:00 and ends May 7th 10:00 I need to be able to show that status time in the Mar, Apr, and May report.

 

[Bat17]

I have a status that starts Mar 2nd 12:00 and ends May 7th 10:00 I need to be able to show that status time in the Mar, Apr, and May report.

Sorry a bit more complex than I first noticed!
Which field is it that you are actualy reporting? will this field need splitting proprtionaly for the months it apears in or do you just want to report it in each month?
When you generate a reoport is it just for the month under consideration ie, April, or is a continous report, March, April, May?

Peter

 

[Gmoz]

I would like my report to look somthing like this

Status time for March

Status1 1day 2hours 15minutes
Status2 7days 8hours 2minutes
Etc...

Report for only month at a time



Thanks

 

[Bat17]

I still can't see where you are pulling the time values from for the report but to pull out a date range that falls in april or overlaps april then you would use a criteria like:-
WHERE (((YourTable.StartDate)<=#4/30/2005#) AND ((YourTable.EndDate)>=#4/1/2005#))

HTH

Peter

 

[Gmoz]

Let me try again I have a record

Status StartDate EndDate
Status1 01/30/2005 12:00 02/02/2005 12:00

Lets say I pull a report for status for the month of march I need the report to say

Jan Status Time
Status1 0days 12hours

Then I pull another report for Feb

Feb Status Time
Status1 1day 12hours

The problem I am having is how to split the time up between the months, using your criteria just gives me a true if the start and end date match the criteria

Thanks again

 

[DaveJHPT]

I think you'll either have to change the way you store your data so that if a period spans more than one month then there is one record for each monthly bit, or calculate the proportion of each period in the month you're looking at on the fly: it's a bit messy, but you might be able to do it in a query, otherwise you'll have to do it in code and populate a temporary table for the report.

Dave

 

[Bat17]

I think I understand you now

you will still need the criteria I gave you to pull out the right records, then try this iif statement:-

IIf([StartDate]<#4/1/2005#,IIf([EndDate]<#4/30/2005#+1,[EndDate]-#4/1/2005#,#4/30/2005#-#4/1/2005#+1),IIf([EndDate]<#4/30/2005#+1,[EndDate]-[StartDate],#4/30/2005#+1-[StartDate])) AS timeTaken

This will return the time taken as decimal days but we can worry about formatting once we have the maths right

Peter

 

[Gmoz]

OK I used this in my query

AS timeTaken: IIf([Status_Start_Date/Time]<#4/1/2005#,IIf([Status_End_Date/Time]<#4/30/2005#+1,[Status_End_Date/Time]-#4/1/2005#,#4/30/2005#-#4/1/2005#+1),IIf([Status_End_Date/Time]<#4/30/2005#+1,[Status_End_Date/Time]-[Status_Start_Date/Time],#4/30/2005#+1-[Status_Start_Date/Time]))

I get a weird decimal number, I cant say I really understand what it is doing.

Also will It be possible to pass in the dates that are hard coded in as it is?

Waiting for my next mission sir

 

[skea]

Do you mean sorting/grouping your report by Month and Time!!
or forcing a new page report to go to another Month, in case the times for the previous months are over?

 

[Bat17]

weird decimal numbers is just what we wanted Access stores dates as a number of elapsed days starting back sometime 1900's with the time being a fraction of a day so when we take one date from another it gives the number of days inbetween, if there is a time element you get a decimal. so midnight to dinnertime would be 0.5 days.
What to do with the number depends on what you are using it for, if you are doing more maths on it latter then best leave it as it is and just pretty it up for dispaly at the end. Or we can format it to a text field now in a Days, Hours, Min Sec format.

For the start/end just chop the dates in for a link to a form

so:-
IIf([Status_Start_Date/Time]<#4/1/2005#,

becomes

IIf([Status_Start_Date/Time]<[Forms]![frmMyForm]![StartDate],

Where start date is a field on a form called MyForm and it has been formatted as a date!

I expect that that is all as clear as mud

Peter

 

[Pat Hartman]

You can't do this in a query. You'll need to write VBA to allocate the time to multiple months. If the start day is not equal to the end day -
If Month(Me.StartDate) <> Month(Me.EndDate) Then
you' need to find the amount of time from startdate to the end of the startmonth and then loop through each month.

Here's a sample db I put together that contains many useful date functions. You'll be able to find the functions you need to find the last day of the given month and others that may come in handy.

 

[Gmoz]

I all ready downloaded your DBase and gat some very helpfull info, Thanks!

and then loop through each month.

Could you please show me an example of what the loop procedure would look like. I would greatly appreciate it.

Frank

 

[Bat17]

You can't do this in a query. You'll need to write VBA to allocate the time to multiple months

did you test my query Pat?

Peter

 

[Pat Hartman]

Bat17 - no I didn't for two reasons:
1. hardcoded dates are not useful.
2. A query cannot turn one row into multiples. If the start date is in one month and the end data is in another, one row must be produced for EACH month in the range. In order to produce the recordset needed for the report, a record would need to be created for March and a completely separate record would need to be created for April.

Gmoz, I don't have any code handy. I'll try to put something together over the weekend.

 

[Bat17]

1. hardcoded dates are not useful.

I had already posted how to take the dates from a form, hardcoded dates were only there to prove the concept.

If the start date is in one month and the end data is in another, one row must be produced for EACH month in the range

As I read the question Frank only wanted to pull the data for a specific month from within the available range to create a monthly report, which is what I think my query does

Peter

 

[sonny]

The sample db on the date functions was sweet.

I see there was a way to acount for holidays when calculating business days...

Can this also look at another table that has leave dates?

If I have a start and end for a task and that task is assigned to someone, what I wanted to do was also deduct the days the person may have scheduled leave to get a total count of days this person could work on the task......

The table for leave would have start and end dates as well. So acount for any days that match between the leave tbl and the task dates....