Time to Decimal using Expression

[insomnia79]

Hi All,

I am working on a report that looks at how long a person was logged into an application and the work they compelted during that session.

I have a table with about 5 columns (date, name, total forms, forms hour, time logged in).

In order to keep it all as automated as possbile I am aiming to have the access table process into a query, this will give me a running total of the forms completed and also to change the time field to a decimal. This is so that I can then add up the length of time in a week the person was logged in. However I am struggling to keep the time in a decimal format (originally using this: -

Decimal Time: DatePart("h",[time logged on]) & Format(DatePart("n",[time logged on])/60,".00")

which worked in access nicely. When I went to add the colum to the pivot table it won't sum up the totals for that week.

Where am I going wrong? (Hope this is clear from the explanation I have given on what I am trying to do).

Many thanks

 

[Norie]

Well I'm not 100% sure but I think that expression could be returning text rather than a number.

What data type is the field you are dealing with?

What is it exactly you want to extract from it?

 

[insomnia79]

Hi Norie,

Thanks for the quick reply, here is the query that I am running:

<CENTER><TABLE cellSpacing=0 align=center cellpadding=2px width=50% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 8 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><A HREF="javascript: void(0)" onclick="myFunc('768673122','Main_Data_Linked_To_Excel_Graphs : ','Select Query, Record Count : 558');" title="Select Query, Record Count : 558"><font color="White">Main_Data_Linked_To_Excel_Graphs : Select Query</font></A></TD><TD CLASS="AccTBInner" align=right >Access 2003</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccDataElem" colSpan= 8 ><B>SELECT</B> <BR> tbl_Alias.Date<BR>, tbl_Alias.Verifier<BR>, tbl_Alias.[Time Logged On]<BR>, DatePart("h",[time logged on]) & Format(DatePart("n",[time logged on])/60,".00") AS [Decimal Time]<BR>, tbl_Alias.[Total Daily Forms]<BR>, tbl_Alias.[Average Daily Forms Per Hour]<BR>, (<B>SELECT</B> <BR> Sum([main].[Total daily Forms]) AS Total
<BR><B>FROM</B> <BR> [main]
<BR><B>WHERE</B> <BR> ((([main].[guid])<=[tbl_Alias].[guid])) AS [Running Total]
<BR><B>FROM</B> <BR> main AS tbl_Alias
<B>ORDER BY</B> <BR> tbl_Alias.Date<BR>, tbl_Alias.Verifier;
</TD></TR><TR ><TD CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('768673122','Date : ','Date/Time');" title="Date/Time">Date</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('768673122','Verifier : ','Text(255)');" title="Text(255)">Verifier</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('768673122','Time Logged On : ','Date/Time');" title="Date/Time">Time Logged On</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('768673122','Decimal Time : ','Text(0)');" title="Text(0)">Decimal Time</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('768673122','Total Daily Forms : ','Double');" title="Double">Total Daily Forms</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('768673122','Average Daily Forms Per Hour : ','Double');" title="Double">Average Daily Forms Per Hour</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" onclick="myFunc('768673122','Running Total : ','Double');" title="Double">Running Total</A></TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><img src='AccArr.gif' width='7' height='7'/></TD><TD CLASS="AccDataElem" >02/04/2007</TD><TD CLASS="AccDataElem" >hollowaye</TD><TD CLASS="AccDataElem" >08:53:28</TD><TD CLASS="AccDataElem" >8.88</TD><TD CLASS="AccDataElem" >494</TD><TD CLASS="AccDataElem" >56</TD><TD CLASS="AccDataElem" >494</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >02/04/2007</TD><TD CLASS="AccDataElem" >montgomery</TD><TD CLASS="AccDataElem" >05:30:06</TD><TD CLASS="AccDataElem" >5.50</TD><TD CLASS="AccDataElem" >206</TD><TD CLASS="AccDataElem" >37</TD><TD CLASS="AccDataElem" >700</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >02/04/2007</TD><TD CLASS="AccDataElem" >robertsr</TD><TD CLASS="AccDataElem" >06:24:01</TD><TD CLASS="AccDataElem" >6.40</TD><TD CLASS="AccDataElem" >421</TD><TD CLASS="AccDataElem" >66</TD><TD CLASS="AccDataElem" >1121</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >02/04/2007</TD><TD CLASS="AccDataElem" >tuckerc</TD><TD CLASS="AccDataElem" >05:52:38</TD><TD CLASS="AccDataElem" >5.87</TD><TD CLASS="AccDataElem" >304</TD><TD CLASS="AccDataElem" >52</TD><TD CLASS="AccDataElem" >1425</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >03/04/2007</TD><TD CLASS="AccDataElem" >hollowaye</TD><TD CLASS="AccDataElem" >08:51:36</TD><TD CLASS="AccDataElem" >8.85</TD><TD CLASS="AccDataElem" >528</TD><TD CLASS="AccDataElem" >60</TD><TD CLASS="AccDataElem" >1953</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >03/04/2007</TD><TD CLASS="AccDataElem" >king</TD><TD CLASS="AccDataElem" >00:03:50</TD><TD CLASS="AccDataElem" >0.05</TD><TD CLASS="AccDataElem" >6</TD><TD CLASS="AccDataElem" >94</TD><TD CLASS="AccDataElem" >1959</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >03/04/2007</TD><TD CLASS="AccDataElem" >montgomery</TD><TD CLASS="AccDataElem" >06:33:38</TD><TD CLASS="AccDataElem" >6.55</TD><TD CLASS="AccDataElem" >246</TD><TD CLASS="AccDataElem" >37</TD><TD CLASS="AccDataElem" >2205</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >03/04/2007</TD><TD CLASS="AccDataElem" >strongl</TD><TD CLASS="AccDataElem" >05:10:31</TD><TD CLASS="AccDataElem" >5.17</TD><TD CLASS="AccDataElem" >195</TD><TD CLASS="AccDataElem" >38</TD><TD CLASS="AccDataElem" >2400</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >04/04/2007</TD><TD CLASS="AccDataElem" >hollowaye</TD><TD CLASS="AccDataElem" >07:22:22</TD><TD CLASS="AccDataElem" >7.37</TD><TD CLASS="AccDataElem" >350</TD><TD CLASS="AccDataElem" >47</TD><TD CLASS="AccDataElem" >2750</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >04/04/2007</TD><TD CLASS="AccDataElem" >montgomery</TD><TD CLASS="AccDataElem" >07:24:39</TD><TD CLASS="AccDataElem" >7.40</TD><TD CLASS="AccDataElem" >331</TD><TD CLASS="AccDataElem" >45</TD><TD CLASS="AccDataElem" >3081</TD></TR><TR ><TD ID="IE" CLASS="AccIEOnlyMain" colSpan= 8 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccButton" ><img src='AccNavL.jpg' width='40' height='15'/></TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccButton" ><img src='AccNavR.jpg' width='40' height='15'/></TD><TD CLASS="AccIEOnlyInnerLeft" > of 558</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 8 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data768673122" CLASS="AccInfoBarInnerRight" >Main_Data_Linked_To_Excel_Graphs</TD><TD ID="Info768673122" CLASS="AccInfoBarData" >Select Query, Record Count : 558</TD></TR></TABLE></TD></TR><TR ><TD CLASS="tpkrow" colSpan = 8 ><CENTER><font size = 1>Access HTML Add-in provided by theprimarykey.com</font></CENTER></TD></TR></TABLE></CENTER>

What I want to then do is have the table linked to excel pivot tables (working well so far) and one of the tables with be the total of the decimal time, given me an indication of how long that person was logged in for.

Hope this clarifies things.

 

[Norie]

I'm afraid it doesn't really.

Why do you need to get the 'decimal time'?

The time in Access if the field type is date/time is already a decimal.

That's how both Access and Excel store dates/times.

Also I'm a little confused as how you could calculate elapsed time with only the logged in/on time, unless you have a fixed point for the end time.

 

[Andrew Fergus]

Hi

The following expression (in a query) should give you the decimal time you are seeking :

Decimal Time : CDbl(Hour([Mytime]) + (Minute([MyTime])/60) + (Second([MyTime])/3600))

Given time values are already stored by Access as decimal values, the following shorter expression should yield the same result:

NewTime : 24*CDbl([MyTime])
or
NewTime : 24*[MyTime]

HTH, Andrew

 

[insomnia79]

Re: Time to Decimal using Expression - SOLVED

Hi both, thanks for the replies.

Sorry for not explaining it too well. I think the problem was with Excel in the end. Having taking your suggestions that the time in Access is a decimal anyway, I looked again at the pivot table. The process is as follows: -

Each week I get a report which has the daily hours each person has worked. This is eg. 07:28:34 (hh, m, s) so they should of been working or at least logged in for just under 7.5 hours.

Then this is entered into access together with the form's per hour and the total forms per day.

This then links to a pivot table in excel.

The pivot table then groups by week (monday to sunday) the SUM of the total forms per user, the hourly average per user and (this was where I was getting stuck) the total time logged in for that week by user.

Reason for using excel in this way was to make the graphs easier to manage and work with and for making the reports look better.

Solution! Was to look at the pivot table and add the logged in time to the value section. Then change this to a SUM and make the number into hh:mm:ss. Before I was hoping to get a value of say 37.5 if they were logged in for 37 and a hlaf hours, now I actually get the time and it does look alot better (more meaningful in the final report too).

Perhaps this should of been an Excel question instead but at least now I have learnt something new in Access as well.

Thanks for your input. TC.