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

[falcondeer]

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

I am sorry to say it is not working, may be I committed a mistake some where !

See the pics

 

[Gasman]

Walk through the code with F8 and inspect the variables.

 

[arnelgp]

check also the Table, is it PT_SessionDate or "PT SessionDate"

 

[The_Doc_Man]

When you see 12:00:00 AM, that is the Access time-conversion routine's way of saying "0" so you have a zero value being returned.

 

[Pat Hartman]

Keep in mind that if the recordset is not in sequence by date, this will not work!!!!! Just add controls in the footer of the form to get the min() and max() values for date and a third control to find the difference. THAT works REGARDLESS of how the recordset is sorted.

 

[gemma-the-husky]

Pat is right.

Databases deal with sets of data as a whole, rather than dealing with a collection of records individually in the way that excel (for instance) presents them. That's why Access is blazingly fast at dealing with large sets of data, in a way that excel just isn't, but on the other hand it's really difficult to do some of the things that are trivial in Excel. Access knows there are a certain number of records in your set of data, and it doesn't care what order they are in. It can calculate totals and other things in terms of the whole set blazingly quickly. But it can't compare records 1 and 2, because to all intents, there aren't records 1 and 2 - just the entire set of say, 945 records

Trying to consider the relationship between 2 distinct rows in a database such as first and last, or current and next is difficult and ephemeral. As soon as you change the display order, or filter records, the original records you were considering have moved, or maybe are no longer available.
That's why it's hard to show a running total in access form.

For some things, you need a different way of approaching the problem that avoids relying on operations that aren't readily available. It's easier to do that, than to try to force Access to do things it isn't built for, as that can slow your dbs performance drastically.

I mean, what does the difference in dates between the first and last record actually show you?

 

[MajP]

Please read what @Pat Hartman posted, there is no reason this should be done in code. If you want to add a little more flexibilty, simply build the already mentioned aggregate query that returns two fields. Patient ID and days between max and min. Then you can do a simple dlookup in a calculated control or make a wrapper function.

Public Function GetDaysBetween(PatientID)
GetDaysBetween = dlookup("DaysBetween","Myquery", "PatientID = "me.patientID
end function

 

[arnelgp]

the reason for recordset is simple.
your form can be bound to a table, a query or a select statement, right?
you might even have a Special routine to Filter the resulting
recordset before passing it to the Form.

on this scenario you cannot use any Domain function since
the recordset is not obtained using a Table or Query.

 

[Pat Hartman]

I didn't suggest writing code. I suggested using the ControlSource property of three controls rather than writing code that moves the form's recordsource back and forth between two nebulous points. You want the Min() date and the Max() date. That's pretty straightforward. Using First() and Last() is a rookie mistake when people don't understand that recordsets are not linear the way flat files are. They are unordered sets. You can sort them initially ascending by date but the user can change the sort order and that would impact the results and he might never know. Using my suggestion keeps the code from breaking should the user do the unexpected.

= Min(yourDate)
= Max(yourDate)
= DateDiff("d", Control1, Control2)

OR, just use a single control if you prefer
= DateDiff("d", Min(yourDate), Max(yourDate))

 

[gemma-the-husky]

I just re-read your first post. Given your problem, one possible way is to look at your dbs design.

rather then patient 1 ---- n treatments have
patient 1 -----n patientcase 1 ----- n treatments

This way, you put details about this case including the start date/referral date in the patient case record, and the case length is todays date (or however you determine the case closed date) compared with the stored start date. You don't need a first and last record. The case is either open or closed, and you know the case start date from the patientcase record. Doing it with multiple records must be duplicating data, and breaking normalisation rules somewhere, I would have thought.

You can't put this information in the patient record, because the patient will have more than one incident/case