# Thread: Calculating days since last visit (Access97 SR2)

1. ## Calculating days since last visit (Access97 SR2)

Can anyone suggest a more elegant way to calculate the number of days between visits,
i.e. I have a table with the dates of visits to various sites, and I want to calculate the number of days between the last visit and the one before that.

I could write some VB code but I wonder if there's a better way.

2. ## Re: Calculating days since last visit (Access97 SR

I don't know what fields are holding these values, but the DateDiff function should handle this.
ElapseDays = DateDiff("d",[LastVisit],[VisitBeforeThat])

3. ## Re: Calculating days since last visit (Access97 SR

Thomas, yes the DateDiff is the function I'd use to calculate the days, but the question is how to generate the LastVisit and VisitBeforeThat parameters!
The table only has the site number and visit date in it, so I'd like to come up with SQL to find the previous visit to the last visit for each site - my suspicion is that this is 'procedureal' so I need code, but I'm wondering if there is a better way

4. ## Re: Calculating days since last visit (Access97 SR

You can do it with SQL by finding the DMAX date that's less than the DMAX visit date for that person. That's the method I'd use. Using VBA or not really depends on what you're going to do with the data, i.e., print it out, pass it to something else, trigger some other action, etc.

5. ## Re: Calculating days since last visit (Access97 SR

Steve,

this could be a start of a function :
<pre>Function Elapsed() As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dtLastVisit As Date
Dim dtPrevVisit As Date
Set db = CurrentDb
strSQL = "SELECT TOP 2 VisitDate FROM tblVisit " _
& "ORDER BY VisitDate DESC;"
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
dtLastVisit = rst!VisitDate
rst.MoveLast
dtPrevVisit = rst!VisitDate
Elapsed = DateDiff("d", dtPrevVisit, dtLastVisit)
End Function
</pre>

You can to add selection criteria you want as arguments

hope this help

6. ## Re: Calculating days since last visit (Access97 SR

Thanks for the pointer, I think I've solved it in a single SQL, but on the really data (on the Oracle server) it's very slow. I tried another approach (three stacked queries) and it was MUCH faster, like <3 sec's compared to >1 minute!
I suppose it's all the subselects and DateDiff

SELECT o.CaseNo, o.PlotNo, (Select Max(VisitDate) from tblVisits i where i.CaseNo = o.caseno and i.Plotno = o.plotno group by caseno) AS MaxOfVisitDate, Min(DateDiff("d",[VisitDate],[MaxOfVisitDate])) AS Elapsed
FROM tblVisits AS o
WHERE (((o.VisitDate)<(Select Max(VisitDate) from tblVisits i where i.CaseNo = o.Caseno and i.Plotno = o.plotno group by caseno)))
GROUP BY o.CaseNo, o.PlotNo
ORDER BY o.CaseNo;

7. ## Re: Calculating days since last visit (Access97 SR

If your data is actually in Oracle (or SQL Server for that matter), it would be faster to execute the SQL on the Server in its version of SQL rather than using Access SQL, which is the longest, slowest way since it uses the Jet query engine instead of the server's query engine.

8. ## Re: Calculating days since last visit (Access97 SR

Appreciate that Charlotte, I'd like to do it, but the database goes out to a number of sites, and they all have different Oracle set-ups, so I'd have to put in some mechanism to refresh the ODBC connections, so as a stop-gap, I've gone with the use of the Access query

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•