Lost in time!

[Duncs]

I'm having difficulty in understanding times (although I've used them
many times before) and getting the results in a query.

Here's what I have. I have a db of accounts that have been worked by
agents. What I want to do, is total up the number of accounts worked
by each agent, and then sum up the time taken to work all accounts.
So, from the following:

Worked_By Date_Started Date_Completed
barikp 02/11/2009 06:20:16 02/11/2009 07:39:17
barikp 02/11/2009 07:39:31 02/11/2009 08:01:44
barikp 04/11/2009 05:17:32
barikp 09/11/2009 09:08:52 09/11/2009 09:35:11
barikp 09/11/2009 09:35:16 09/11/2009 10:09:40
barikp 09/11/2009 10:09:44

So, for this agent I am only interested in four entries, those with
the Date Completed field filled in. Doing this the old-fashioned way,
I would get:

Date_Started Date_Completed Duration
02/11/2009 06:20 02/11/2009 07:39 01:19
02/11/2009 07:39 02/11/2009 08:01 00:22
09/11/2009 09:08 09/11/2009 09:35 00:26
09/11/2009 09:35 09/11/2009 10:09 00:34

Giving a total of 02:41 to work.

What I now have a problem with is totalling up a large number of
accounts. One agent has taken a total of 25 hours and 31 minutes to
work accounts. In Access this shows as 1.063738426, which I can
understand is the way Access displays its time values. What I need to
do is, display this hh:mm in the results of a query. I've tried using
the format function, but it doesn't seem to work.

Anyone help?

Many TIA

Duncs

 

[Duncs]

As an addition, my SQL code is as follows:

SELECT tblActionDetails.Worked_By, Count
(tblActionDetails.Date_Started) AS TotalAccountsWorked, Min
(tblActionDetails.Date_Started) AS [Earliest Account Worked], Max
(tblActionDetails.Date_Completed) AS [Latest Account Worked], Sum
([Date_Completed]-[Date_Started]) AS TotalWorkTime
FROM tblActionDetails
WHERE (((tblActionDetails.Date_Completed) Is Not Null))
GROUP BY tblActionDetails.Worked_By
HAVING (((tblActionDetails.Worked_By) Is Not Null));

As can be seen it's the statement "Sum([Date_Completed]-
[Date_Started]) AS TotalWorkTime" that I want to be displayed using
hours greater than 24.

Duncs

 

[vanderghast]

int( CDec(24)* TotalWorkTime ) & ":" & Format( TotalWorkTime, "nn:ss" )


should to, for positive TotalWorkTime, even if it makes more than 24 hours
(or 12 hours if your setting use AM/PM).


Vanderghast, Access MVP


As an addition, my SQL code is as follows:

SELECT tblActionDetails.Worked_By, Count
(tblActionDetails.Date_Started) AS TotalAccountsWorked, Min
(tblActionDetails.Date_Started) AS [Earliest Account Worked], Max
(tblActionDetails.Date_Completed) AS [Latest Account Worked], Sum
([Date_Completed]-[Date_Started]) AS TotalWorkTime
FROM tblActionDetails
WHERE (((tblActionDetails.Date_Completed) Is Not Null))
GROUP BY tblActionDetails.Worked_By
HAVING (((tblActionDetails.Worked_By) Is Not Null));

As can be seen it's the statement "Sum([Date_Completed]-
[Date_Started]) AS TotalWorkTime" that I want to be displayed using
hours greater than 24.

Duncs

 

[Duncs]

Vanderghast,

Still can't get this. My SQL now looks like this:

SELECT
tblActionDetails.Worked_By,
Count(tblActionDetails.Date_Started) AS TotalAccountsWorked,
Min(tblActionDetails.Date_Started) AS [Earliest Account Worked],
Max(tblActionDetails.Date_Completed) AS [Latest Account Worked],
Sum([Date_Completed]-[Date_Started]) AS TotalWorkTime,
Int( CDec(24* TotalWorkTime)) & ":" & Format( TotalWorkTime, "nn:ss" )
as Duration
FROM tblActionDetails
WHERE (((tblActionDetails.Date_Completed) Is Not Null))
GROUP BY tblActionDetails.Worked_By
HAVING (((tblActionDetails.Worked_By) Is Not Null));

I wasn't sure whether you had made a typo or not, as you originally
had the new line as:

int( CDec(24)* TotalWorkTime ) & ":" & Format( TotalWorkTime,
"nn:ss" )

with the parenthesis around the 24, which I thought should have been
around the expression as a whole. her way, when I try to execute the
query, I get an error:

Wrong number of arguments used with function in query expression 'Int
( CDec(24* TotalWorkTime)) & ":" & Format( TotalWorkTime, "nn:ss" )'.

If I put the code in as you suggest, I get the error message:

The expression you entered has a function containing the wrong number
of arguments.

With the highlight appearing on the closing parenthesis of the CDec
(24). As far as I can see, CDec only takes the one parameter, which
makes me think that the parenthesis is in the wrong place. Now, I
always get the message advising of the wrong number of parameters.

What am I doing wrong? I'm using Access 2003 and Date_Started &
Date_Completed are defined as Date fields, holding both the date and
time.

Help!

Duncs

int( CDec(24)* TotalWorkTime ) & ":" & Format( TotalWorkTime, "nn:ss" )

should to, for positive TotalWorkTime, even if it makes more than 24 hours
(or 12 hours if your setting use AM/PM).

Vanderghast, Access MVP


As an addition, my SQL code is as follows:

SELECT tblActionDetails.Worked_By, Count
(tblActionDetails.Date_Started) AS TotalAccountsWorked, Min
(tblActionDetails.Date_Started) AS [Earliest Account Worked], Max
(tblActionDetails.Date_Completed) AS [Latest Account Worked], Sum
([Date_Completed]-[Date_Started]) AS TotalWorkTime
FROM tblActionDetails
WHERE (((tblActionDetails.Date_Completed) Is Not Null))
GROUP BY tblActionDetails.Worked_By
HAVING (((tblActionDetails.Worked_By) Is Not Null));

As can be seen it's the statement "Sum([Date_Completed]-
[Date_Started]) AS TotalWorkTime" that I want to be displayed using
hours greater than 24.

Duncs

I'm having difficulty in understanding times (although I've used them
many times before) and getting the results in a query.

Here's what I have. I have a db of accounts that have been worked by
agents. What I want to do, is total up the number of accounts worked
by each agent, and then sum up the time taken to work all accounts.
So, from the following:

Worked_By Date_Started Date_Completed
barikp 02/11/2009 06:20:16 02/11/2009 07:39:17
barikp 02/11/2009 07:39:31 02/11/2009 08:01:44
barikp 04/11/2009 05:17:32
barikp 09/11/2009 09:08:52 09/11/2009 09:35:11
barikp 09/11/2009 09:35:16 09/11/2009 10:09:40
barikp 09/11/2009 10:09:44

So, for this agent I am only interested in four entries, those with
the Date Completed field filled in. Doing this the old-fashioned way,
I would get:

Date_Started Date_Completed Duration
02/11/2009 06:20 02/11/2009 07:39 01:19
02/11/2009 07:39 02/11/2009 08:01 00:22
09/11/2009 09:08 09/11/2009 09:35 00:26
09/11/2009 09:35 09/11/2009 10:09 00:34

Giving a total of 02:41 to work.

What I now have a problem with is totalling up a large number of
accounts. One agent has taken a total of 25 hours and 31 minutes to
work accounts. In Access this shows as 1.063738426, which I can
understand is the way Access displays its time values. What I need to
do is, display this hh:mm in the results of a query. I've tried using
the format function, but it doesn't seem to work.

Anyone help?

Many TIA

Duncs- Hide quoted text -

- Show quoted text -

 

[vanderghast]

In the debug/immediate window, try:

? int( CDec(24)* 1.1323 ) & ":" & Format( 1.1323, "nn:ss" )

27:10:31


CDec(24) forces the compiler to use a decimal value, rather than an
integer, so that the multiplication is performed by keeping a maximum of
precision. Basically, it is just there to avoid rounding problem. You can
remove it too:


? int( 24* 1.1323 ) & ":" & Format( 1.1323, "nn:ss" )

27:10:31

But you may get rounding problem (although I have no example to supply for
illustration). It would be useless to force the decimal representation after
the multiplication is done, ie:

int(cdec( x * y ))

since the rounding error we wish to avoid would have already occurred.

The problem may be due to the TotalWorkTime being NULL (but that is not
very probable), or that you have a problem of references, or that you don't
use a coma as list delimiter (some countries use ; by default, so it
becomes: Format( variable ; "nn:ss" ) ), or some else that I don't see at
the moment.



Vanderghast, Access MVP



Vanderghast,

Still can't get this. My SQL now looks like this:

SELECT
tblActionDetails.Worked_By,
Count(tblActionDetails.Date_Started) AS TotalAccountsWorked,
Min(tblActionDetails.Date_Started) AS [Earliest Account Worked],
Max(tblActionDetails.Date_Completed) AS [Latest Account Worked],
Sum([Date_Completed]-[Date_Started]) AS TotalWorkTime,
Int( CDec(24* TotalWorkTime)) & ":" & Format( TotalWorkTime, "nn:ss" )
as Duration
FROM tblActionDetails
WHERE (((tblActionDetails.Date_Completed) Is Not Null))
GROUP BY tblActionDetails.Worked_By
HAVING (((tblActionDetails.Worked_By) Is Not Null));

I wasn't sure whether you had made a typo or not, as you originally
had the new line as:

int( CDec(24)* TotalWorkTime ) & ":" & Format( TotalWorkTime,
"nn:ss" )

with the parenthesis around the 24, which I thought should have been
around the expression as a whole. her way, when I try to execute the
query, I get an error:

Wrong number of arguments used with function in query expression 'Int
( CDec(24* TotalWorkTime)) & ":" & Format( TotalWorkTime, "nn:ss" )'.

If I put the code in as you suggest, I get the error message:

The expression you entered has a function containing the wrong number
of arguments.

With the highlight appearing on the closing parenthesis of the CDec
(24). As far as I can see, CDec only takes the one parameter, which
makes me think that the parenthesis is in the wrong place. Now, I
always get the message advising of the wrong number of parameters.

What am I doing wrong? I'm using Access 2003 and Date_Started &
Date_Completed are defined as Date fields, holding both the date and
time.

Help!

Duncs

int( CDec(24)* TotalWorkTime ) & ":" & Format( TotalWorkTime, "nn:ss" )

should to, for positive TotalWorkTime, even if it makes more than 24 hours
(or 12 hours if your setting use AM/PM).

Vanderghast, Access MVP


As an addition, my SQL code is as follows:

SELECT tblActionDetails.Worked_By, Count
(tblActionDetails.Date_Started) AS TotalAccountsWorked, Min
(tblActionDetails.Date_Started) AS [Earliest Account Worked], Max
(tblActionDetails.Date_Completed) AS [Latest Account Worked], Sum
([Date_Completed]-[Date_Started]) AS TotalWorkTime
FROM tblActionDetails
WHERE (((tblActionDetails.Date_Completed) Is Not Null))
GROUP BY tblActionDetails.Worked_By
HAVING (((tblActionDetails.Worked_By) Is Not Null));

As can be seen it's the statement "Sum([Date_Completed]-
[Date_Started]) AS TotalWorkTime" that I want to be displayed using
hours greater than 24.

Duncs

I'm having difficulty in understanding times (although I've used them
many times before) and getting the results in a query.

Here's what I have. I have a db of accounts that have been worked by
agents. What I want to do, is total up the number of accounts worked
by each agent, and then sum up the time taken to work all accounts.
So, from the following:

Worked_By Date_Started Date_Completed
barikp 02/11/2009 06:20:16 02/11/2009 07:39:17
barikp 02/11/2009 07:39:31 02/11/2009 08:01:44
barikp 04/11/2009 05:17:32
barikp 09/11/2009 09:08:52 09/11/2009 09:35:11
barikp 09/11/2009 09:35:16 09/11/2009 10:09:40
barikp 09/11/2009 10:09:44

So, for this agent I am only interested in four entries, those with
the Date Completed field filled in. Doing this the old-fashioned way,
I would get:

Date_Started Date_Completed Duration
02/11/2009 06:20 02/11/2009 07:39 01:19
02/11/2009 07:39 02/11/2009 08:01 00:22
09/11/2009 09:08 09/11/2009 09:35 00:26
09/11/2009 09:35 09/11/2009 10:09 00:34

Giving a total of 02:41 to work.

What I now have a problem with is totalling up a large number of
accounts. One agent has taken a total of 25 hours and 31 minutes to
work accounts. In Access this shows as 1.063738426, which I can
understand is the way Access displays its time values. What I need to
do is, display this hh:mm in the results of a query. I've tried using
the format function, but it doesn't seem to work.

Anyone help?

Many TIA

Duncs- Hide quoted text -

- Show quoted text -

 

[Duncs]

vanderghast,

Entering the formula in th eimmediate window, did indeed give me the
result expected. After some playing around with it, I found that the
formula:

Int(Sum([Date_Completed]-[Date_Started])*24) & ":" & Format(Sum
([Date_Completed]-[Date_Started]),"nn")

As you can see, I've left out the CDec, which seemed to be causing the
problem. Don't know what the issue is / was, but it's fixed now.

Thanks for all your help.

Duncs

In the debug/immediate window, try:

    ? int( CDec(24)* 1.1323 ) & ":" & Format( 1.1323, "nn:ss" )

    27:10:31

CDec(24)  forces the compiler to use a decimal value, rather than an
integer, so that the multiplication is performed by keeping a maximum of
precision. Basically, it is just there to avoid rounding problem. You can
remove it too:

    ? int( 24* 1.1323 ) & ":" & Format( 1.1323, "nn:ss" )

    27:10:31

But you may get rounding problem (although I have no example to supply for
illustration). It would be useless to force the decimal representation after
the multiplication is done, ie:

    int(cdec( x * y ))

since the rounding error we wish to avoid would have already occurred.

The problem may be due to the TotalWorkTime being NULL  (but that is not
very probable), or that you have a problem of references, or that you don't
use a coma as list delimiter (some countries use ;  by default, so it
becomes:  Format( variable ; "nn:ss" )   ), or some else that I don'tsee at
the moment.

Vanderghast, Access MVP


Vanderghast,

Still can't get this.  My SQL now looks like this:

SELECT
tblActionDetails.Worked_By,
Count(tblActionDetails.Date_Started) AS TotalAccountsWorked,
Min(tblActionDetails.Date_Started) AS [Earliest Account Worked],
Max(tblActionDetails.Date_Completed) AS [Latest Account Worked],
Sum([Date_Completed]-[Date_Started]) AS TotalWorkTime,
Int( CDec(24* TotalWorkTime)) & ":" & Format( TotalWorkTime, "nn:ss" )
as Duration
FROM tblActionDetails
WHERE (((tblActionDetails.Date_Completed) Is Not Null))
GROUP BY tblActionDetails.Worked_By
HAVING (((tblActionDetails.Worked_By) Is Not Null));

I wasn't sure whether you had made a typo or not, as you originally
had the new line as:

int( CDec(24)* TotalWorkTime ) & ":" & Format( TotalWorkTime,
"nn:ss" )

with the parenthesis around the 24, which I thought should have been
around the expression as a whole.  her way, when I try to execute the
query, I get an error:

Wrong number of arguments used with function in query expression 'Int
( CDec(24* TotalWorkTime)) & ":" & Format( TotalWorkTime, "nn:ss" )'.

If I put the code in as you suggest, I get the error message:

The expression you entered has a function containing the wrong number
of arguments.

With the highlight appearing on the closing parenthesis of the CDec
(24).  As far as I can see, CDec only takes the one parameter, which
makes me think that the parenthesis is in the wrong place.  Now, I
always get the message advising of the wrong number of parameters.

What am I doing wrong?  I'm using Access 2003 and Date_Started &
Date_Completed are defined as Date fields, holding both the date and
time.

Help!

Duncs

int( CDec(24)* TotalWorkTime ) & ":" & Format( TotalWorkTime, "nn:ss" )

should to, for positive TotalWorkTime, even if it makes more than 24 hours
(or 12 hours if your setting use AM/PM).

Vanderghast, Access MVP

"Duncs" <[email protected]> wrote in message

As an addition, my SQL code is as follows:

SELECT tblActionDetails.Worked_By, Count
(tblActionDetails.Date_Started) AS TotalAccountsWorked, Min
(tblActionDetails.Date_Started) AS [Earliest Account Worked], Max
(tblActionDetails.Date_Completed) AS [Latest Account Worked], Sum
([Date_Completed]-[Date_Started]) AS TotalWorkTime
FROM tblActionDetails
WHERE (((tblActionDetails.Date_Completed) Is Not Null))
GROUP BY tblActionDetails.Worked_By
HAVING (((tblActionDetails.Worked_By) Is Not Null));

As can be seen it's the statement "Sum([Date_Completed]-
[Date_Started]) AS TotalWorkTime" that I want to be displayed using
hours greater than 24.

Duncs

I'm having difficulty in understanding times (although I've used them
many times before) and getting the results in a query.
Here's what I have. I have a db of accounts that have been worked by
agents. What I want to do, is total up the number of accounts worked
by each agent, and then sum up the time taken to work all accounts.
So, from the following:
Worked_By Date_Started Date_Completed
barikp 02/11/2009 06:20:16 02/11/2009 07:39:17
barikp 02/11/2009 07:39:31 02/11/2009 08:01:44
barikp 04/11/2009 05:17:32
barikp 09/11/2009 09:08:52 09/11/2009 09:35:11
barikp 09/11/2009 09:35:16 09/11/2009 10:09:40
barikp 09/11/2009 10:09:44
So, for this agent I am only interested in four entries, those with
the Date Completed field filled in. Doing this the old-fashioned way,
I would get:
Date_Started Date_Completed Duration
02/11/2009 06:20 02/11/2009 07:39 01:19
02/11/2009 07:39 02/11/2009 08:01 00:22
09/11/2009 09:08 09/11/2009 09:35 00:26
09/11/2009 09:35 09/11/2009 10:09 00:34
Giving a total of 02:41 to work.
What I now have a problem with is totalling up a large number of
accounts. One agent has taken a total of 25 hours and 31 minutes to
work accounts. In Access this shows as 1.063738426, which I can
understand is the way Access displays its time values. What I need to
do is, display this hh:mm in the results of a query. I've tried using
the format function, but it doesn't seem to work.
Anyone help?
Many TIA
Duncs- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -