Solved: Time Format in Access

[kbsudhir]

Guys,

I have badlu stuck here.

Below is the data which my query is giving me. "TH: [Total_Login_Hours]-[Overall_BreakDuration]"

1.9537963

The query iscalculating total login hours in a week & deducting from that the total hours of break taken.

Now when I am converting the above value in access using "TotalHours: Format([Total_Login_Hours]-[Overall_BreakDuration],"HH:MM:SS")" then below is the data I am getting.

22:53:28

Which is completely wrong.

Now If I copy the data into excel & manually fromat the data to "HH:MM:SS" then excel gives me the below data which is correct.

46:53:28


Where I am going wrong?
Why Access formating of "HH:MM:SS" giving teh wrong data while excel gives teh correct one.
I am using the same loging for otehr queries in the same access project & all of them are working correctly.
Please guide.

:help :help :help
:dunno :dunno :dunno
:banghead: :banghead: :banghead:
:think: :think: :think:
: pray2: : pray2: : pray2: : pray2:

Thanks
Sudhir

[OBP]

First of all Multiply by 24,it is in days and you want it in hours.
But I am not sure it will work as Access Time formatting usually uses the 24 Hour Clock, not hours minutes and Seconds.
So you may have to break it down yourself Using Int() and multiplying the remainders by 60.
i.e. Int ((1.9537963*24 - int(1.9537963*24)) * 60)
gives the Minutes

[kbsudhir]

Oh.
Firstly I got the answer on why rest of my queries are providing correct info except this one is as you told that access use 24Hour clock and all other queries are below 24Hours. Hence they provide correct data.

But this query crosses 24Hr limit hence all the info stops before 24Hr.

Okay,

I will try your solution out & let you know.

Thanks for your guidance.

Regards
Sudhir

[hansup]

Below is the data which my query is giving me. "TH: [Total_Login_Hours]-[Overall_BreakDuration]"

1.9537963 That value is approximately 2 days.



Now when I am converting the above value in access using "TotalHours: Format([Total_Login_Hours]-[Overall_BreakDuration],"HH:MM:SS")" then below is the data I am getting.

22:53:28 Format expects to receive a "datetime" value. You gave it your number of days. Display the full date and time to see how your Format expression was evaluated:

Format(1.9537963, "yyyy-m-d hh:nn:ss")
1899-12-31 22:53:28


Now If I copy the data into excel & manually fromat the data to "HH:MM:SS" then excel gives me the below data which is correct.

46:53:28If that's what you want, try this code:
Public Function Sudhir(pDays As Double) As String
Dim lngSeconds As Long
Dim intMinutes As Integer
Dim intHours As Integer
Const SecondsPerHour As Integer = 3600
Const SecondsPerMinute As Integer = 60

lngSeconds = pDays * SecondsPerHour * 24
intHours = lngSeconds \ SecondsPerHour
lngSeconds = lngSeconds Mod SecondsPerHour
intMinutes = lngSeconds \ SecondsPerMinute
lngSeconds = lngSeconds Mod SecondsPerMinute

Sudhir = intHours & ":" & intMinutes & ":" & lngSeconds
End Function

[OBP]

Neat :beerchug:

[kbsudhir]

Thanks, the code provides the required data correctly.

Output of the code is what i want but I will not able to display the same in a query.

If I am not wrong, I thinks there is no way of doing this in a query..??? (Stupid Question)

[OBP]

Yes you can, but you need 4 columns Hours, Minutes, Seconds and then Combined where you put them together like the
intHours & ":" & intMinutes & ":" & lngSeconds

[hansup]

You have a query which includes this field expression:

TotalHours: Format([Total_Login_Hours]-[Overall_BreakDuration],"HH:MM:SS")

Replace it with this:

TotalHours: Sudhir([Total_Login_Hours]-[Overall_BreakDuration])

[kbsudhir]

Thanks, it works perfectly Hans.

OBP - Thanks for getting my access clock concept correct.

:bow: :bow: :bow: :bow:

Regards
Sudhir