datediff between first and last row in subform (1 Viewer)

[falcondeer]

Hi

Can somebody help me to calculate the datediff between the first and last row in a subform as shown in the picture.
I want to calculate the difference between the first session (Initial Assessment) 2.9.2020 and the last session (DC) 8.9.2020.
The goal is to know how many days the patient stayed in Physical therapy.

Thanks

 

[Gasman]

I would use the recordsetclone of the form.
When first opened save the date in that record as firstdate
Then MoveLast and save the date in that record as lastdate
Then subtract one from the other and adjust depending on whether you include a day or not.?

HTH

 

[falcondeer]

I would use the recordsetclone of the form.
When first opened save the date in that record as firstdate
Then MoveLast and save the date in that record as lastdate
Then subtract one from the other and adjust depending on whether you include a day or not.?

HTH

Thanks for the quick reply, in fact I am not expert in this, so could you please show me how to write the code.

 

[Gasman]

You will need to adjust for your form and control names.
I just quickly created a form and added this code.

You also need to allow for subform reference, http://www.fmsinc.com/free/NewTips/Access/accesstip26.asp

Private Sub cmdCalcsDays_Click()
Dim rst As DAO.Recordset
Dim iDays As Integer
Dim dtStart As Date, dtEnd As Date

Set rst = Me.RecordsetClone
dtStart = rst.Fields("TransactionDate")
rst.MoveLast
dtEnd = rst.Fields("TransactionDate")
Me.txtDays = dtEnd - dtStart

Set rst = Nothing
End Sub

HTH

 

[Pat Hartman]

Make sure that the subform is sorted in the order that will make first and last meaningful to the calculation.

 

[falcondeer]

You will need to adjust for your form and control names.
I just quickly created a form and added this code.

You also need to allow for subform reference, http://www.fmsinc.com/free/NewTips/Access/accesstip26.asp

Private Sub cmdCalcsDays_Click()
Dim rst As DAO.Recordset
Dim iDays As Integer
Dim dtStart As Date, dtEnd As Date

Set rst = Me.RecordsetClone
dtStart = rst.Fields("TransactionDate")
rst.MoveLast
dtEnd = rst.Fields("TransactionDate")
Me.txtDays = dtEnd - dtStart

Set rst = Nothing
End Sub

HTH

Thanks a lot Gasman.

 

[falcondeer]

Make sure that the subform is sorted in the order that will make first and last meaningful to the calculation.

Thank you

 

[vhung]

Can somebody help me to calculate the datediff between the first and last row in a subform as shown in the picture.
I want to calculate the difference between the first session (Initial Assessment) 2.9.2020 and the last session (DC) 8.9.2020.
The goal is to know how many days the patient stayed in Physical therapy.

several ways to do it
>try this way
>use query to sql
SELECT [PhysicalTherapy].groupyear, First([PhysicalTherapy].sessiondate) AS FirstOfdate, Last([PhysicalTherapy].sessiondate) AS LastOfdate, [LastOfdate]-[FirstOfdate] AS diffdays
FROM PhysicalTherapy
GROUP BY [PhysicalTherapy].groupyear;
>but have to add field as groupyear = 2020, for this year
>see the TableQuery attachment below
>might use msgbox to view result

 

[Pat Hartman]

vhung,
First() and Last() are not relevant concepts in a relational database. What is first and what is last? Tables and queries are defined as UNORDERED sets so first or last is always indeterminate. However, if you are working with a SORTED recordset, you can order the data into a meaningful sequence and as you scroll forward and backward, the order will remain constant as it does with a flat file and so therefore, predictable. Domain functions do not work with ordered sets and so never return predictable First/last records. Now, you're going to go out to your 20 row table and you are going to try prove me wrong. You're going to think that the expected record is ALWAYS returned. Once you start working with large recordsets and live data which is actually updated, you might see the issue or you can just read the MS documentation which will give you the same warning.

The reason people don't see this problem in testing is because their recordsets are too small, and none of the updated records have moved from the spot they were originally inserted in. The biggest reason Access developers are fooled by this is because of the compact process. One of the functions of a compact is to reorder all records back int physical sequence by PK.

FYI, Here's a link to an article that explains the issue in detail.

ORDER BY - First, Last and always!

Sorting the results of SQL Queries is trivially simple but you need to do it explicitly to avoid unexpexted results.

codekabinett.com

 

[vhung]

vhung,
First() and Last() are not relevant concepts in a relational database. What is first and what is last? Tables and queries are defined as UNORDERED sets so first or last is always indeterminate. However, if you are working with a SORTED recordset, you can order the data into a meaningful sequence and as you scroll forward and backward, the order will remain constant as it does with a flat file and so therefore, predictable. Domain functions do not work with ordered sets and so never return predictable First/last records. Now, you're going to go out to your 20 row table and you are going to try prove me wrong. You're going to think that the expected record is ALWAYS returned. Once you start working with large recordsets and live data which is actually updated, you might see the issue or you can just read the MS documentation which will give you the same warning.

The reason people don't see this problem in testing is because their recordsets are too small, and none of the updated records have moved from the spot they were originally inserted in. The biggest reason Access developers are fooled by this is because of the compact process. One of the functions of a compact is to reorder all records back int physical sequence by PK.

FYI, Here's a link to an article that explains the issue in detail.

ORDER BY - First, Last and always!

Sorting the results of SQL Queries is trivially simple but you need to do it explicitly to avoid unexpexted results.

codekabinett.com

well if that would be, so let it be
>i guess yes "recordsets are too small"

 

[falcondeer]

You will need to adjust for your form and control names.
I just quickly created a form and added this code.

You also need to allow for subform reference, http://www.fmsinc.com/free/NewTips/Access/accesstip26.asp

Private Sub cmdCalcsDays_Click()
Dim rst As DAO.Recordset
Dim iDays As Integer
Dim dtStart As Date, dtEnd As Date

Set rst = Me.RecordsetClone
dtStart = rst.Fields("TransactionDate")
rst.MoveLast
dtEnd = rst.Fields("TransactionDate")
Me.txtDays = dtEnd - dtStart

Set rst = Nothing
End Sub

HTH

I know this is might be weird but I just tried this code today and it is not working with me,

The following is what I did:

Private Sub cmdCalcsDays_Click()

Dim rst As DAO.Recordset
Dim iDays As Integer
Dim dtStart As Date
Dim dtEnd As Date
Set rst = Me.RecordsetClone

dtStart = Forms![tblPatient]![tblPhysiotherapy Subform].Form![PT_SessionDate]
rst.MoveLast
dtEnd = Forms![tblPatient]![tblPhysiotherapy Subform].Form![PT_SessionDate]

Me.txtDays = dtEnd - dtStart

Set rst = Nothing

End Sub

When I put the cursor on both dtStart and dtEnd it reads "12:00:00 AM" , and the result in the text box is "0"

Any idea what's wrong

Thanks

 

[arnelgp]

don't use Forms![tblPatient]![tblPhysiotherapy Subform].Form
remember, you are using Recodset, just use

dtStart = rst![PT_SessionDate]
rst.MoveLast
dtEnd = rst![PT_SessionDate]
Me.txtDays = DateDiff("d", dtStart, dtEnd)

 

[Gasman]

I know this is might be weird but I just tried this code today and it is not working with me,

The following is what I did:

Private Sub cmdCalcsDays_Click()

Dim rst As DAO.Recordset
Dim iDays As Integer
Dim dtStart As Date
Dim dtEnd As Date
Set rst = Me.RecordsetClone

dtStart = Forms![tblPatient]![tblPhysiotherapy Subform].Form![PT_SessionDate]
rst.MoveLast
dtEnd = Forms![tblPatient]![tblPhysiotherapy Subform].Form![PT_SessionDate]

Me.txtDays = dtEnd - dtStart

Set rst = Nothing

End Sub

When I put the cursor on both dtStart and dtEnd it reads "12:00:00 AM" , and the result in the text box is "0"

Any idea what's wrong

Thanks

Yes, you are using the same field for both, hence 0.

 

[Pat Hartman]

I forgot to mention in my reply to vhung that simply changing First() to Min() and Last() to Max() would actually work. First and Last are totally dependent on row order and can be different from one execution to the next. However, Min and Max are precise and do not produce different results depending on the order of the recordset.

 

[Cronk]

@falcondeer wrote:
I know this is might be weird but I just tried this code today and it is not working with me,

The following is what I did:

Private Sub cmdCalcsDays_Click()
.....
Set rst = Me.RecordsetClone

If that code is for a control on the main form, it will be opening a recordset based on that form's recordsource. To refer to the subform's recordset, you need

Set rst = Me![tblPhysiotherapy Subform].Form.RecordsetClone

 

[falcondeer]

don't use Forms![tblPatient]![tblPhysiotherapy Subform].Form
remember, you are using Recodset, just use

dtStart = rst![PT_SessionDate]
rst.MoveLast
dtEnd = rst![PT_SessionDate]
Me.txtDays = DateDiff("d", dtStart, dtEnd)

Hi

Same problem, I still get "0"

See pic.

Thanks

 

[falcondeer]

If that code is for a control on the main form, it will be opening a recordset based on that form's recordsource. To refer to the subform's recordset, you need

Set rst = Me![tblPhysiotherapy Subform].Form.RecordsetClone

I used it but same thing, I get "12:00:00 AM" in dtStart and dtEnd.
See pic..

Thanks

 

[Cronk]

In what form do dtStart and dtEnd are located? In which event and in which form is the code running? Does the code actually run?

 

[arnelgp]

Private Sub cmdCalcDays_Click()
    Dim rst As DAO.Recordset
    Dim iDays As Integer
    Dim dtStart As Date
    Dim dtEnd As Date
    
    dtStart = #1/1/9999#
    dtEnd = #12/31/1899#

    Set rst = Forms![tblPatient]![TblPhysiotherapy Subform].Form.RecodsetClone
    ' the following code will work whether the recordset is unsorted/sorted desc or asc on PT_SessionDate
    With rst
        If .RecordCount > 0 Then
            .MoveFirst
            Do Until .EOF
                dtStart = IIF(![PT_SessionDate] <= dtStart, ![PT_SessionDate], dtStart)
                dtEnd = IIF(![PT_SessionDate] >= dtEnd, ![PT_SessionDate], dtEnd)
                .MoveNext
            Loop
            iDays = DateDiff("d", dtStart, dtEnd)
        End If
    End With
    Set rst = Nothing
    Me![txtDays] = iDays
End Sub

 

[falcondeer]

In what form do dtStart and dtEnd are located? In which event and in which form is the code running? Does the code actually run?

dtStart and dtEnd are two variable declared in the VB code which reads the first row and last row of "PT_SessionDate" field in the sub-sub form which is located in the main form.