Question Difference + Time + Date + Format

[kohbengleong]

Hi Anyone who can help me,

I need to have a field that returns the difference between the [Closed Date and Time] and the [Created Date and Time]. Then the difference will be in the format of hhhh:nn:ss. That means if the difference is more then 1000hours it will show in hours, mins and secs instead of months, days, hours, mins and seconds.

I did a query which returns TimeDif by using:

TimeDif: ([Closed Date and Time]-[Created Date and Time]) but i am not sure how to change the format of the field TimeDif to the one i want which is in hours mins and secs. As shown in the file i attached, the TimeDif is in decimal form. Which i later learnt from some of the posts is that the numbers before the decimal point actually refers to the days and months and the numbers after the decimal point refers to the hours mins and seconds. Please correct me if i am wrong.

My question here is that how can i create this field which will return to me the date time difference in just hours mins and secs? I had no problem with this in excel as i could custom format to [h]:mm:ss. But for Access i still have not found any clue so far.

The data i got stems from Oracle CRM on Demand.

Will apreciate a lot if someone can help me out here.

 

[raskew]

Hi -

Welcome to the forum.

Take a look at http://www.access-programmers.co.uk/forums/showthread.php?t=183059 for a similar problem.

Here's an adaptation of that, hopefully returning hh:nn:ss.
Note that I've used US date/time format, i.e. mm/dd/yyyy vs. dd/mm/yyyy.

Public Function hhnnss_diff(dtein As Date, dteout As Date) As String

're: http://www.access-programmers.co.uk/forums/showthread.php?p=917847#post917847
'*******************************************
'Purpose:   Returns DateDiff as hh:nn:ss
'Coded by:  raskew
'Input:     From debug (immediate) window:
'           1) ? hhnnss_diff(#04/08/2009 17:20:00#, #07/23/2009 09:35:34#)
'Output:    1) 2536:15:34
'*******************************************
Dim x As Double

   x = DateDiff("s", dtein, dteout)
   hhnnss_diff = Format(x \ 3600, "00") & ":" & Format((x Mod 3600) \ 60, "00") & ":" & Format(x Mod 60, "00")

End Function

HTH - Bob

 

[kohbengleong]

Hi Bob,

As i am a total noob in access, please kindly advise how do i go about using the code? Do i paste it in Macro or Modue?

I am currently using access 2003 on windows XP. Many thanks!

 

[raskew]

Hi again -

Paste the code into a standard module, ensuring that the module name
is not the same as the function name.

You can then call it from any code.

Bob