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

Check the Help files for specifics and use the DMin, DMax
and DateDiff methods. Your code should look something
like this:

Dim strCaseIDVariable as String
Dim dtFirst as Date
Dim dtLast as Date
Dim intDiff as Integer

strCaseIDVariable = [ControlUsedToSelectCaseID]

dtFirst = DMin("[DateField]","tblFirstTable", _
"[CaseID]='" & strCaseIDVariable & "'")

dtLast = DMax("[DateField]","tblSecondTable", _
"[CaseID]='" & strCaseIDVariable & "'")

intDiff = DateDiff("d", dtFirst, dtLast)

and you should be able to display the difference in a
MessageBox or control:

MsgBox "This account has been active " & intDiff & " days."
OR
[txtDaysSinceLastOrder] = intDiff

Hope this helps!

Howard Brody


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