• Calculating days since last visit (Access97 SR2)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Calculating days since last visit (Access97 SR2)

    Author
    Topic
    #359937

    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

    Viewing 0 reply threads
    Author
    Replies
    • #540813

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

      • #540961

        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

        • #540966

          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.

          • #541195

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

            • #541252

              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.

            • #542311

              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

        • #540971

          Steve,

          this could be a start of a function :

          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
          

          You can to add selection criteria you want as arguments

          hope this help

    Viewing 0 reply threads
    Reply To: Calculating days since last visit (Access97 SR2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: