Calculate date differnce in different tables, min/max

[Melissa]

I want to calculate the difference between first date in one table and last date in another table, where the records are linked by a unique case number. Does anyone know the expression I can use to do that?
Thanks so much in advance!

Melissa

 

[tony]

Here you go:

DateDiff: [Date1] - [Date2]

-----Original Message-----
I want to calculate the difference between first date in

one table and last date in another table, where the
records are linked by a unique case number. Does anyone
know the expression I can use to do that?

 

[e stewart]

-----Original Message-----
I want to calculate the difference between first date in

one table and last date in another table, where the
records are linked by a unique case number. Does anyone
know the expression I can use to do that?

Thanks so much in advance!

Melissa
.
the command is the datdiff("n",date1,date2)

The following is the possible options for "n"
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

 

[Melissa]

Thanks, but how do you take the min of one table date and the max of the other?
Like: What is the period between the patient's initial date of surgery and the last date they were seen?
Much appreciated.

 

[e stewart]

-----Original Message-----
Thanks, but how do you take the min of one table date and the max of the other?
Like: What is the period between the patient's initial

date of surgery and the last date they were seen?

Much appreciated.
.
you can use a make table query on each table to find the

max or min using the max or min function in the group by
line . Then you can either use vb code or another query to
link the tables and use the datdiff function.

 

[Yvonneb]

I don't think it can be done in 1 query.

You can create a function and call it from Forms, Queries and Reports

Create a Module
Create a Function that calls the 2 queries to return the 2 dates, then performs the DateDiff

Public Function GetDaysSinceSeen() as integer
' execute 1st query to get the SurgeryDate

' execute 2nd query to get the DateSeen

GetDaysSinceSeen = DateDiff("d",DateSeen, SurgeryDate)
End Function

Good Luck!