Calculating date differences between multiple rows..possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anybody know if this is possbile? Here is an example of what I'm trying
to solve:

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

Can a report (or anything) run a calculation between the end date and the
start date on the next row. I'm just trying to find out if there is a 90 day
gap.
Any ideas on this one?

Thanks!!!
-JB
 
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]

' I didn't try this code, so you might need to play with it a bit
 
This will compare the end date on row 1 with the start date on row 2 and give
me that difference?

Ofer said:
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]

' I didn't try this code, so you might need to play with it a bit

--
I hope that helped
Good luck


JB said:
Does anybody know if this is possbile? Here is an example of what I'm trying
to solve:

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

Can a report (or anything) run a calculation between the end date and the
start date on the next row. I'm just trying to find out if there is a 90 day
gap.
Any ideas on this one?

Thanks!!!
-JB
Click to expand...
Click to expand...
 
It should, except of the first line.

--
I hope that helped
Good luck


JB said:
This will compare the end date on row 1 with the start date on row 2 and give
me that difference?

Ofer said:
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]

' I didn't try this code, so you might need to play with it a bit

--
I hope that helped
Good luck


JB said:
Does anybody know if this is possbile? Here is an example of what I'm trying
to solve:

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

Can a report (or anything) run a calculation between the end date and the
start date on the next row. I'm just trying to find out if there is a 90 day
gap.
Any ideas on this one?

Thanks!!!
-JB
Click to expand...
Click to expand...
Click to expand...
 
You will have to forgive me, my programming skills are rusty.
I created a blank text field in the details section of the report names
"txtDateDiff"
After this line it wants a Then or GoTo statement, it gives an error if you
have the And statement in there: If not isnull(MyStartDate)

For reference the start date on the report is: "DATE OF ENTRY"
The end date is: "End_Employ_Date"

Any ideas on that??
Thanks!
 
Also, let's say there is more than one person, is there a way so it doesn't
calculate into the next person's?

Here is what I have so far...

Option Compare Database
Dim MyStartDate
Dim MyEndDate


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
MyStartDate = Me.PLACEMENT_DATE
If Not IsNull(MyStartDate) Then
Me.txtDateDiff = DateDiff("d", MyStartDate, MyEndDate)
End If
MyEndDate = Me.End_Employ_Date

End Sub
 
Try SQL of the following to get the number of days between employment:

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;
 
Try this

Option Compare Database
Dim MyStartDate
Dim MyEndDate


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
MyStartDate = Me.PLACEMENT_DATE
If Not IsNull(MyStartDate) And Not IsNull(MyEndDate) Then
Me.txtDateDiff = DateDiff("d", MyStartDate, MyEndDate)
End If
MyEndDate = Me.End_Employ_Date

End Sub

In the grouping and sorting, create a group to persons, and create a header
section, in the format event of this section create the code

MyStartDate=null
MyEndDate=null
 
Back
Top