Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Thanks in advance

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Francois

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •