Expression error

[Whitney]

I'm getting the following error:
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

I'm using the follwing expression in the query:
Total Handle Time: Sum(DateDiff("s",[Sign Out Time],[Sign In Time]))\3600 &
":" & Format((Sum(DateDiff("s",[Sign Out Time],[Sign In Time])) Mod
3600)\60,"00")

What am I missing?

 

[BruceM]

I don't see the point of the Sum function. Also, I think Mod is for VBA
only. What exactly do you wish to see for the output?

 

[Whitney]

It was a resolution I was given back in December, see response chain...

Shouldn't that be

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60 & ":" &
Format(Avg(DateDiff("s",[Sign In Time],[Sign out Time])) Mod 60,"00")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Use the DateDiff function

DateDiff("s",[Sign In Time],[Sign out Time])

The average would be
Avg(DateDiff("s",[Sign In Time],[Sign out Time]))

To DISPLAY that in minutes and seconds
Avg(DateDiff("s",[Sign In Time],[Sign out Time])) is Seconds

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60
is minutes

So putting that all together.

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60 & ":" &
Format(Avg(DateDiff("s",[Sign In Time],[Sign out Time])),"00")

Total handle time
Sum(DateDiff("s",[Sign In Time],[Sign out Time]))

Expressing that as hours and minutes

Sum(DateDiff("s",[Sign In Time],[Sign out Time]))\3600 & ":" &
Format((Sum(DateDiff("s",[Sign In Time],[Sign out Time])) Mod 3600)
\60,"00")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

In the query I have the following:
Handle Time: [Sign In Time]-[Sign Out Time]

Example:
Sign In Time 7:00:00 AM - Sign Out Time 6:00:00 AM = Total Handle Time
1:00

Now if I divide that by the number processed 22 = 02:44 (nn:ss) Average
Handle Time per email.

However, when I run the report for a longer date range, the Total Handle
Time exceeds a 24 hour period, so I have to use the expression below to
show total hours. When I use this expression, the Average Handle Time
gives an error. Is there a way to have it reference the sum of the Total
Handle Time and then divide it by the number processed?

How do you do that?
The DateDiff function only returns one value, Years, Months, Weeks,
Days, Hours, Minutes, or Seconds. It will not return both.

If you can post the code where you populate the field. And the data
type of the field, I will be happy to help.

I'm trying to calculating an averge handle time using total number of
processed emails divided by the total handle time. However, since the time
could be longer than 24 hours, I'm using the following expression
=Int(CSng(Sum([Handle Time])*24)) & ":" & Int(CSng(Sum([Handle
Time])*1440))-Int(CSng(Sum([Handle Time])*24))*60 & ":" &
Int(CSng(Sum([Handle Time])*86400))-Int(CSng(Sum([Handle Time])*1440))*60
This is causing the average field to have an error. How can I correct that?

Additionally, I would like the total handle time to be in an hh:nn format,
but it won't save. It keeps changing back to Short Time, which displays
hh:nn:s. Is there any way to correct this?

I don't see the point of the Sum function. Also, I think Mod is for VBA
only. What exactly do you wish to see for the output?

I'm getting the following error:
This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

I'm using the follwing expression in the query:
Total Handle Time: Sum(DateDiff("s",[Sign Out Time],[Sign In Time]))\3600
&
":" & Format((Sum(DateDiff("s",[Sign Out Time],[Sign In Time])) Mod
3600)\60,"00")

What am I missing?

 

[Marshall Barton]

I'm getting the following error:
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

I'm using the follwing expression in the query:
Total Handle Time: Sum(DateDiff("s",[Sign Out Time],[Sign In Time]))\3600 &
":" & Format((Sum(DateDiff("s",[Sign Out Time],[Sign In Time])) Mod
3600)\60,"00")

What am I missing?

It looks ok to me.

OTTH, it shouldn't make any difference, but I would write it
this way:

DateDiff("n", Sum(DateDiff("s",[Sign Out Time],[Sign In
Time]))\60 & Format((Sum(DateDiff("s",[Sign Out Time],[Sign
In Time])) Mod 60), "\:00")

Double check to make sure the time fields really are
Date/Time type.

 

[Whitney]

Now I'm getting an error that says it's missing a closing parenthesis,
bracket or vertical bar, so I added a third closing parenthesis after the
first Sign In Time, but then I get a message that it has a function
containing the wrong number of arguments.

I'm getting the following error:
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

I'm using the follwing expression in the query:
Total Handle Time: Sum(DateDiff("s",[Sign Out Time],[Sign In Time]))\3600 &
":" & Format((Sum(DateDiff("s",[Sign Out Time],[Sign In Time])) Mod
3600)\60,"00")

What am I missing?

It looks ok to me.

OTTH, it shouldn't make any difference, but I would write it
this way:

DateDiff("n", Sum(DateDiff("s",[Sign Out Time],[Sign In
Time]))\60 & Format((Sum(DateDiff("s",[Sign Out Time],[Sign
In Time])) Mod 60), "\:00")

Double check to make sure the time fields really are
Date/Time type.

 

[Marshall Barton]

Let's try it with fewer copy/paste fumbles:

Sum(DateDiff("n",[Sign Out Time],[Sign In Time]))\60 &
Format(Sum(DateDiff("n",[Sign Out Time],[Sign In Time])) Mod
60, "\:00")
--
Marsh
MVP [MS Access]

Now I'm getting an error that says it's missing a closing parenthesis,
bracket or vertical bar, so I added a third closing parenthesis after the
first Sign In Time, but then I get a message that it has a function
containing the wrong number of arguments.

I'm getting the following error:
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

I'm using the follwing expression in the query:
Total Handle Time: Sum(DateDiff("s",[Sign Out Time],[Sign In Time]))\3600 &
":" & Format((Sum(DateDiff("s",[Sign Out Time],[Sign In Time])) Mod
3600)\60,"00")

What am I missing?

It looks ok to me.

OTTH, it shouldn't make any difference, but I would write it
this way:

DateDiff("n", Sum(DateDiff("s",[Sign Out Time],[Sign In
Time]))\60 & Format((Sum(DateDiff("s",[Sign Out Time],[Sign
In Time])) Mod 60), "\:00")

Double check to make sure the time fields really are
Date/Time type.

 

[Whitney]

Thank you that worked.

Now I have two additional questions.
#1 On the report I'm trying to sum the Total Handle Time. On the detail
footer I added a field with =sum([Total Handle Time]). I get the message that
the expression is too complex. I have several other sums set up the same way
and they are fine, however this is the only time field. All others are just
numbers.

#2 I would also like to get an Average Handle Time, by dividing Total Handle
Time by the number Processed. Using the following expression =sum([Total
Handle Time]/[Processed]) I get the same error that it's too complex in
either the query or the report.

What am I missing?

Now I'm getting an error that says it's missing a closing parenthesis,
bracket or vertical bar, so I added a third closing parenthesis after the
first Sign In Time, but then I get a message that it has a function
containing the wrong number of arguments.

I'm getting the following error:
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

I'm using the follwing expression in the query:
Total Handle Time: Sum(DateDiff("s",[Sign Out Time],[Sign In Time]))\3600 &
":" & Format((Sum(DateDiff("s",[Sign Out Time],[Sign In Time])) Mod
3600)\60,"00")

What am I missing?

It looks ok to me.

OTTH, it shouldn't make any difference, but I would write it
this way:

DateDiff("n", Sum(DateDiff("s",[Sign Out Time],[Sign In
Time]))\60 & Format((Sum(DateDiff("s",[Sign Out Time],[Sign
In Time])) Mod 60), "\:00")

Double check to make sure the time fields really are
Date/Time type.

 

[Marshall Barton]

Whitney wrote:nk you that worked.

Now I have two additional questions.
#1 On the report I'm trying to sum the Total Handle Time. On the detail
footer I added a field with =sum([Total Handle Time]). I get the message that
the expression is too complex. I have several other sums set up the same way
and they are fine, however this is the only time field. All others are just
numbers.

#2 I would also like to get an Average Handle Time, by dividing Total Handle
Time by the number Processed. Using the following expression =sum([Total
Handle Time]/[Processed]) I get the same error that it's too complex in
either the query or the report.

I can't tell from that. In general, that kind of issue can
be difficult to track down. It often goes all the way bacj
to the report's record source query.

The only common problem similar to that (but I think it has
a different message) is trying to use an aggregate funtion
on a control instead of a field in the record source
table/query.