HELP! Elapsed Time Difference

[accessineedhelp]

So, on my form, I want two fields:



1) Incident Start Date and Time
2) Incident End Date and Time

Then, when the user enters in the information and clicks a "Calculate Elapsed Time" button, I want a 3rd field to populate with the elapsed time between the two Date and Times from the 1st and 2nd field.

I need help with all of this... how do I even start? I have made an input mask for the 1st and 2nd field of 00/00/0000 00:00 (so it would come out as something like 12/24/2010 15:34). It works when I go from design view to form view to try it, then when I click somewhere else it shows the output as 12/24/2010 3:34:00 PM.

That's all fine. But how do I calculate the difference so that the 3rd field shows something like "2 days, 3 hours, 48 minutes, 3 seconds".... or total hours like "67 hr, 48 min, 2 sec"... I don't really care right now what the output looks like in that 3rd field, I just want some sort of difference calculation.

Please help, many thanks!!!

[accessineedhelp]

Also - it would be nicer if I had a date field and a time field separately for the 1st field and the 2nd field... I just thought it might be easier to calculate the differences if it was just between two fields and not four. Thoughts? The usability would be better if I could separate the date and time for each field because users would be able to click on the date picker icon instead of writing the date using my input mask...

This is for a work project I need to have done by tomorrow.... I need help!!!!

[KrisDdb]

You can calculate it using the 'DateDiff' function. Try this

DateDiff ( interval, date1, date2, [firstdayofweek], [firstweekofyear])

The 'firstdayofweek' and 'firstweekofyear' are optional. The 'interval' part should be substituted by any of the following: "yyyy","q","m","y","d","w","ww","h","n","s". They stand for year, quarter, month.. etc. And make sure you give the dates in between two # symbols.

Example: DateDiff("yyyy",#date1#,#date2#) would return no of years as difference.

HTH

[accessineedhelp]

So under the "On Event" click of the "Calculate Elapsed Time Button" I put this:

[Elapsed Incident Time] = DateDiff("hh:nn:ss",#[Incident Start Date and Time]#,#[Incident End Date and Time]#)

But while I am still in the VB code builder a message comes up while highlighting the first # sign, saying "Compile Error: Expected: Expression"

What is wrong with what I did????

[accessineedhelp]

I also posted this thread in "Forms" - but can you please take a look at my form so you can see what I mean? P.S. - I have now separated the date and time (looks better)... A link to the image is here:

http://i1111.photobucket.com/albums/...work1/form.jpg

[rpeare]

[Elapsed Incident Time] = DateDiff("hh:nn:ss",#[Incident Start Date and Time]#,#[Incident End Date and Time]#)

I have never attempted to use datediff using the "hh:nn:ss" format, but I don't think it will work. You'll need to break it down

Start with days

datediff("d", [StartTime],[EndTime])

you do not need the # marks for this formula, because you are using datediff it is assuming the fields are date/time fields.

Next you want your hours

datediff("h", [StartTime], [EndTime])

but you want to remove any whole days from the number of hours so it would become:

datediff("h", [StartTime], [EndTime]) -(datediff("d", [StartTime],[EndTime])*24)

then on down the line to the smaller time increments. Try creating some text boxes on your form to calculate the time differences first, then attempt to concantenate them into a single field. Just remember at each step you have to include a subtraction of all whole units of the next higher time level, so for seconds you will have to have a formula that does something like this:

Total Seconds Elapsed
MINUS (Total Days Elapsed Calculation * Total Seconds in a Day)
MINUS (Total Hours Elapsed Calculation * Total Seconds in an Hour)
MINUS (Total Minute Elapsed Calculation * Total Seconds in an Minute)

where your elapsed calculation would be the actual formulas you devise on your form, you would just substitute in the entire calculation.