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?
