Calculating gaps between date on different rows

[jeffshex]

This one has me stumped.
I am trying to figure out the amount of days between dates. Its' just the dates are on different rows.

Name     Employment     StartDate     EndDate
===============================================
Jon Doe       Job1      7/22/05        8/15/05
Jon Doe       Job2      8/18/05        9/01/05
Jon Doe       Job3      2/01/06

I'm trying to figure out the gap between the EndDate of one job to the StartDate between the next. Basically calculating the days of unemployment.
--------------------
Someone posted this code, but I'm having trouble with it.

1. Create a blank field in the details section, to contain the different
2. Create to parameters in the declaretion section of the report
Dim MyStartDate, MyEndDate
3. On the on format event of the detail section you can write the code
section

MyEndDate = Me.[StartDate]
If not isnull(MyStartDate) and not isnull(MyEndDate) then
Me.BlankFieldName = DateDiff("d",MyStartDate,MyEndDate)
End If
MyEndDate = Me.[EndDate]

Any ideas???!!!
Thanks

 

[lupins46]

The code you have posted calculates the length of the job, not the gaps between jobs.
Are the records numbered in any way or are you just (and I don't mean 'simply') looking for the earliest start date after the current end date?

 

[jeffshex]

Well I have it setup as this:
Each person has an Unique ID assigned to them, so they are grouped by that. Now from there I haven't found a really good way to sort, either by start or end date.
All I'm really trying to do is find people who have a 90 day or greater gap in employment.
So, I am not opposed to try something completely different.
That code kind of works, but the code doesn't "know" to stop calculating at the end of each person.
**The whole thing throwing me off is comparing the end date of one row to the StartDate of the next row.

Hope that helps some, if not..lemme know if there is any other information I can provide.

Thanks!!!

 

[dhookom]

You might be able to use SQL. This query displays the number of days between the most recent end date the the current start date:

SELECT tblEmployment.*, 
[StartDate]-Nz(
  (SELECT Max(EndDate) 
   FROM tblEmployment e 
   WHERE e.UniqueID = tblEmployment.UniqueID AND e.EndDate <=tblEmployment.StartDate)
  ,[StartDate]) AS UnemployedDays
FROM tblEmployment;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

couple questions on that one Duane.
1. I currently do not have a field named "Unemployed Days". Will this query auto make that field for me?

2. If question 1 does not apply...I am getting a syntax error and there is some stuff in this query that I have not seen before (Like the "e"). It gives the error on the expression line.

SELECT tblPlacements.[CLIENT ID], tblPlacements.[LAST NAME], tblPlacements.[FIRST NAME], tblPlacements.[COMPANY], tblPlacements.[PLACEMENT DATE], tblPlacements.[End_Employ_Date], 
[PLACEMENT DATE]-Nz(
  (SELECT Max(End_Employ_Date) 
   FROM tblPlacements e 
   WHERE e.CLIENT ID = tblPlacements.CLIENT ID AND e.End_Employ_Date <=tbPlacements.PLACEMENT DATE)
  ,[PLACEMENT DATE]) AS UnemployedDays
FROM tblPlacements;

Do you spot something that I missed??

 

[dhookom]

You missed "Naming Conventions 101" where the class learned to not place spaces in field names ;-)
You are calculating the field UnemployedDays from your data:

SELECT tblPlacements.[CLIENT ID], 
  tblPlacements.[LAST NAME],
  tblPlacements.[FIRST NAME],
  tblPlacements.[COMPANY],
  tblPlacements.[PLACEMENT DATE],
  tblPlacements.[End_Employ_Date], 
  [PLACEMENT DATE]-Nz(
   (SELECT Max(End_Employ_Date) 
    FROM tblPlacements e 
    WHERE e.[CLIENT ID] = tblPlacements.[CLIENT ID] AND
      e.End_Employ_Date <=tbPlacements.[PLACEMENT DATE])
    ,[PLACEMENT DATE]) AS UnemployedDays
FROM tblPlacements;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

hmm, it kind of works. Downside is that it won't let me group it in the report because of the subquery.
Some people are showing with a number in unemployed days even though they only have one entry.

 

[dhookom]

You could possibly create a temporary table based on the previous SQL or you could substitute the very slow DMax() for the subquery:

SELECT tblPlacements.[CLIENT ID], 
  tblPlacements.[LAST NAME],
  tblPlacements.[FIRST NAME],
  tblPlacements.[COMPANY],
  tblPlacements.[PLACEMENT DATE],
  tblPlacements.[End_Employ_Date], 
  [PLACEMENT DATE]-Nz(DMax("End_Employ_Date", "tblPlacements", "[CLIENT ID] = " & [CLIENT ID] & " AND End_Employ_Date <=#" & [PLACEMENT DATE] & "#")
    ,[PLACEMENT DATE]) AS UnemployedDays
FROM tblPlacements;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

That make table off that query is looking pretty good.
I grouped them by client ID and it looks like the numbers are showing accurately.
Do you think that if it is filtered any more, like by showing only duplicate ID entires, or by Program, it will affect the days between?

Thanks!

 

[dhookom]

Try it. I don't have all your data to give it a go.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]