Calculate date differnce in different tables, min/max

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Here you go:

DateDiff: [Date1] - [Date2]

-----Original Message-----
I want to calculate the difference between first date in
Click to expand...
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?
 
-----Original Message-----
I want to calculate the difference between first date in
Click to expand...
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)
Click to expand...
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
 
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.
 
-----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
Click to expand...
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
Click to expand...
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.
 
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!
 
Back
Top