Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


DateDiff to calculate time elapsed between current and other records.

Posted on 2003-03-11
Medium Priority
Last Modified: 2013-12-25

Am using an adodb recordset to pass values from a set of masked edit boxes into a sql table.  The purpose of this exercise is to log tasks and their duration in a time recording program.  One of the edit boxes therefore passes to a 'duration' field.  However if a user does not enter a duration, I would like the program to automatically calculate what the duration is.

I assume i use the DateDiff function, but don't know how to go about picking up values from records other than the current one.

I have tried declaring a 'duration' string in the DateDiff expression and picking up values for it by moving between records, but get 'invalid procedure call or argument' error.

heres the code:

Private Sub cmdUpdate_Click()
    'The code for date and time can be input as date and time and then joined
    'in a string to bring both items together, that string is then added to the
    'recordset as the whole date and time.
    Dim ShortDate As String
    Dim ShortTime As String
    Dim DateTime As String
    Dim DurCalc As String
    Dim NextDur As String

    'in my database table i have fields start_date_time, cost_centre, task_code, duration
    DateTime = Format(ShortDate, "dd/mm/yyyy") & " " & Format(ShortTime, "hh:mm:ss")
    rec.Fields(1) = DateTime
    rec.Fields(2) = Format(mebCCentre.Text, ">")
    rec.Fields(3) = Format(mebTask.Text, ">")
    If rec.EOF = True Then
        rec.Fields(4) = Format(mebDur.Text, "0")
        NextDur = rec.Fields(1)
        rec.Fields(4) = DateDiff("ssss", rec.Fields(1), NextDur)
    End If
End Sub
Question by:isupsn
  • 2

Expert Comment

ID: 8112272
Not quite clear on what you are trying to do, but you could consider using the Date function to get the current system date and time....

If however your application is not the app that is performing the tasks then that would be no use.

Otherwise - if the user does not enter a duration where are you getting this field from? I.E. how do you intend calculating a duration when you don't have a finish time or are not given it - picking the data out of the database should be ok once you have the data to calculate it

Author Comment

ID: 8112403

thanks for taking a look at this for me.  Sorry i haven't explained it very clearly ... i think half my problem is that i'm not thinking it through properly.

i am working on a time recording program.  i use system date and time as you suggest when user is entering real time records, but the bit i'm stuck on is for batch updating, which is generally historic data.

therefore would ideally like the user just to be able to enter dates, times and task codes and for the field 'duration' to update automatically if there is another record with a date and time against which to calculate a task duration (ie. if its not the last record). it should exist with a value of '0' if it is the last record until another record is entered after it at which time it will have a duration and should automatically be updated.


Accepted Solution

rafsalman earned 560 total points
ID: 8112487
ok I think i am getting what you are trying to do.

I am assuming that your app is not running constantly in the background and that you are not constantly monitoring the database

I think the best way would be when the user adds a record for you then to calculate the duration based on system time (this is the end time for the previous task) less the start time of that task.

you can avoid too many difficulties if when the user clicks ok (or something) to enter the new task, prior to adding this to database, calculate the duration of the previous task as you know that there will only be one record with 0 in the duration field.

You can search for this using some SQL. (i.e. search for all the records where duration = 0).  Now update the duration field.  Now add the new record?


Expert Comment

ID: 9084895
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept rafsalman   comment as answer.

Please leave any comments here within the next seven days.
EE Cleanup Volunteer

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question