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

Forget the macro.  All you need do is paste the TimeDuration function I posted into a standard module and call it in a computed column in a query used as the form's RecordSource:

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

or in an unbound text box on the form, with a ControlSource of:

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

And, as I said before, do not have a Missed SLA Time column in the base table.  Always compute the value on the fly as above.  You can then be guaranteed that it will reflect the values in the SLA Time and End Time columns correctly.  If you store values which can be derived from other values in a column, then there is nothing to stop any one of the values being changed independently of the others, resulting in inconsistent data.