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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

770 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