DateDiff returns the wrong information.

Question

I'm trying to claculate the difference between two times in a macro.

I'm using the following command:

DateDiff("n",[Forms]![Retail SLA Form]![SLA Time],[Forms]![Retail SLA Form]![End Time])

The value is to be stored in a field called **[Forms]![Retail SLA Form]![Date]**which is to be stored as a time HH:MM but the value returned is a date 5/4/1899.

Every article that I read says to use DateDiff but for some reason I can't seem to get it to work correctly.

The time I am calculating is 04:00 - 03:00 which should return 01:00.

These times will change depending on the information entered into the two fields - right now I am just using some test data.

Both fields that the calculation is to be performed on is defined in the table as a 'Short Date' and on the form as well.

Does anyone have any ideas as to what I am doing wrong?

Thanks

Answer 1

Thanks - This is the final command that I used:

(DateDiff('n',[Forms]![Retail SLA Form]![SLA Time],[Forms]![Retail SLA Form]![SLA End Time])/1440)

It now returns the correct time value.

Answer 2

The problem is you are formatting the control as a date value, not a time value. The DateDiff does not return a time value. It returns the number of minutes as an integer value. To get it to be a time value you would have to divide by 1440 (the number of minutes in a day).

Answer 3

Hopefully this explaniation will help.

I have a database that collects run times for jobs. Some of the jobs start before midnight and can end either before midnight or after midnight.

Each job has a Service Level Agreement attached to it and depending on the job the SLA can either be before midnight or after.

I have multiple fields on the page - Here are the major ones:

SLA Name

Start Time

End Time

SLA Time

Missed SLA Time

Since some of the jobs run in a different time zone I need to convert the times to central time so I do have fields with the name converted as well.

Start Time - Convetred, End Time - Converted and SLA Time - Converted.

What I need to do is to calculate the missed SLA time if a job exceeds the SLA time. As long as the job ends at the same time period as the SLA (Example - SLA Time is 02:00 and the job completes at 03:00) then my calculations work correctly using the datediff command above.

Where I am having an issue is if the SLA time is before midnight and the job completes after midnight.

The way I have the form created it actually builds the day's jobs from another table. So today I could have job a, job b, job d, job e and tomorrow I could have job a, job c, job d, job z.

The job I am currently working on is called 'Daily' it has a SLA time of 22:00 and the other day it started at 02:01 and completed at 02:04.

so the following would be true:

SLA Name:               Daily

Start Time:              02:01

End Time:                02:04

SLA Time:               22:00

Missed SLA Time: 04:04

What I am trying to do is to get the macro that I have set up to calculate the Missed SLA time.

The SLA Name and the SLA Time are auto populated from my tables and the  Start Time and End Time are entered in manually by my department.

I hope this helps - if not I might be able to send you a blank copy of the database.

Thanks

Answer 4

As you mention SQL I assume you want to do it in a query.  Once you've added the TimeDuration function to a module in your database you can call it in a query in exactly the same way as you would a built in function.  So, if you want to return the elapsed time between the two times in a computed column in a query you'd put the following in the field row of a blank column in the query design grid:

ElapsedTime: TimeDuration([SLA Time],[SLA End Time])

You can call the column whatever you wish of course, not necessarily ElapsedTime.  You said earlier that the value is to be stored in a control [Forms]![Retail SLA Form]![Date].  If by stored you mean in a field in the underlying table, then don't do that.  You should not have such a field.  It's not only unnecessary but, more importantly, introduces redundancy and the risk of update anomalies.  Return the value in a computed column in a query, which can be the form's RecordSource.  You could alternatively return the value in a computed text box control in the form, using the following expression as its ControlSource property:

=TimeDuration([SLA Time],[SLA End Time])

Where does the [SLA Time - Missed] field come into the picture?  You haven't mentioned that in your earlier posts.

Answer 5

My SQL coding is lousy - where would I substitute the following fields:

[Forms]![AS400 SLA Form]![SLA Time - Missed] - This holds the missed SLA time.

[Forms]![AS400 SLA Form]![SLA Time] - This holds the time value that I do not want to exceed.

[Forms]![AS400 SLA Form]![SLA End Time] - This holds the the value that I want to calculate on.

Is there a way do do this without writing SQL?

Thanks for your help.