datediff between first and last row in subform

[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.

 

[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.

 

[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