Calculate DateDiff between two different records 1

[Debbie37]

The main form is patient admission info and the subform tracks ventilator data. Two date fields, [DateOnVent] and [DateWean], contain one ventilator record. However, when/if the patient returns to vent support I need to calculate days weaned (date returning to vent support [DateOnVent]new vent record-minus- the last date weaned [DateWean] previous vent record). A patient may have several vent records during one admission. Any ideas on how to calculate between two records would recieve my enduring gratitude

 

[MichaelRed]

Are you familiar with SubQueries? If so, this is perhaps the easiest approach. Otherwise, it can be done in code, but it will be somewhat convolouted.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PWise]

see
thread181-461510

 

[PWise]

i created a table with these fields
client 'client number
sdate 'start date
edate ' enddate
in the query i added
nextstart 'the next start date for this client
timeelsaped 'the time elasped between ending date and next date
SELECT dates.client, dates.sdate, dates.edate, DMin("sdate","dates"," sdate>#" & [dates].[edate] & "#and client=" & [dates].[client]) AS nextstart, DateDiff("d",[edate],[nextstart]) AS timeelsaped
FROM dates
WHERE (((dates.client)=1));

 

[Debbie37]

Thank you pwise, it works like a charm!!! I gave you a star for the simplicity of the solution Super-fantastic!

Thanks tons....