Too Complex Query

[ladybug via AccessMonster.com]

I am trying to create a query and I am getting an error that states:

The expression is typed incorrectly, or it is too complex to be evaluated.....


My fields are as follows:
chrUserID
chrProgramID
dtmProdDate
ACDCalls
AvgACD
AvgACWTime
ExtInCalls

I want to sum ACDCalls, AvgACD, AvgACWTime,and ExtInCalls per User and
Program.

When I select "Sum" for the total, I get this error. When I leave it as
"Group By," it shows ProgramID multiple times for each day. I want to sum
the data for these programs together.

I only get the error when I try to "SUM" on AvgACD and AvgACWTime. The data
in these fields are times, ex. 2:29.

Anyone know what I can do to be able to sum these fields per employee and
program?

 

[Duane Hookom]

Could you share your data types of the fields? A WAG suggests you are
attempting to Sum a text field.

 

[ladybug via AccessMonster.com]

Well, I tried changing the data types. When I tried changing it to "Number"
I got the error:

Database encountered errors while converting the data
The Contents of Fields in 603 record(s) were deleted.
Do you want to proceed anyway?

Below is sample data from the field. Anytime I change the data type from
text I get the above error.

AvgACD
:33
2:29
1:50
2:16
1:37
1:44
1:54
:15
2:17

Could you share your data types of the fields? A WAG suggests you are
attempting to Sum a text field.

I am trying to create a query and I am getting an error that states:

[quoted text clipped - 23 lines]

Anyone know what I can do to be able to sum these fields per employee and
program?

 

[John Vinson]

Well, I tried changing the data types. When I tried changing it to "Number"
I got the error:

Database encountered errors while converting the data
The Contents of Fields in 603 record(s) were deleted.
Do you want to proceed anyway?

Below is sample data from the field. Anytime I change the data type from
text I get the above error.

AvgACD
:33
2:29
1:50
2:16
1:37
1:44
1:54
:15
2:17

Are these time/duration values, e.g. minutes and seconds or hours and
minutes? What is 2:29 + 1:50 equal to?

These are certainly NOT numbers which Access will recognize or handle.
You'll need to convert them into a numeric value; for instance, if
they are minutes and seconds, you could create a field Duration (or
ACD or whatever name makes sense) and store seconds by running an
Update query updating the new field to

60*Val(Left("0" & [AvgACD], InStr("0" & [AvgACD], ":")-1)) +
Val(Mid([AvgACD], InStr([AvgACD], ":") + 1))

John W. Vinson[MVP]

 

[nbarnett]

Storing time and date values as a string is not suggested. Also, I've
found that storing time and date as a compounded number (ie, something
with a ":" in the middle of it) is not advisable.

You will find it a lot easier to calculate time and numbers when they
are displayed as integers... ie 60 seconds = 1 minute = 1:00

Save yourself the headache and store the duration as seconds.

to convert a table with times stored as strings, like you have, you
could try a couple of things, and I'm sure there are easier ways...

try pasting this in your immediate window in the VB editor (this is a
really crappy way of doing it, but it works... mostly
? (replace(Left("2:20", 2),":","")*60) + replace(right("2:20",
2),":","")

will give you the number of seconds in 2:20

another way to achieve the same results would be to input the "2:20" as
a string value into a datediff function. To do this, you have to
remember that Access is going to see this as 2:20 AM, so we can just
find the difference, in minutes, from midnight (0:00) and 2:20, and it
will give us the number we're looking for.

put this in your immediate window
? datediff("n",#0:00#,#2:20#)

once you have NUMBERS in the duration columns, averaging them will be
no problem.

Well, I tried changing the data types. When I tried changing it to "Number"
I got the error:

Database encountered errors while converting the data
The Contents of Fields in 603 record(s) were deleted.
Do you want to proceed anyway?

Below is sample data from the field. Anytime I change the data type from
text I get the above error.

AvgACD
:33
2:29
1:50
2:16
1:37
1:44
1:54
:15
2:17

Could you share your data types of the fields? A WAG suggests you are
attempting to Sum a text field.

I am trying to create a query and I am getting an error that states:

[quoted text clipped - 23 lines]

Anyone know what I can do to be able to sum these fields per employee and
program?

 

[nbarnett]

PS, that other dude's replace string is way better since it actually
works if you have a single 0 on the left of the ":"

Storing time and date values as a string is not suggested. Also, I've
found that storing time and date as a compounded number (ie, something
with a ":" in the middle of it) is not advisable.

You will find it a lot easier to calculate time and numbers when they
are displayed as integers... ie 60 seconds = 1 minute = 1:00

Save yourself the headache and store the duration as seconds.

to convert a table with times stored as strings, like you have, you
could try a couple of things, and I'm sure there are easier ways...

try pasting this in your immediate window in the VB editor (this is a
really crappy way of doing it, but it works... mostly
? (replace(Left("2:20", 2),":","")*60) + replace(right("2:20",
2),":","")

will give you the number of seconds in 2:20

another way to achieve the same results would be to input the "2:20" as
a string value into a datediff function. To do this, you have to
remember that Access is going to see this as 2:20 AM, so we can just
find the difference, in minutes, from midnight (0:00) and 2:20, and it
will give us the number we're looking for.

put this in your immediate window
? datediff("n",#0:00#,#2:20#)

once you have NUMBERS in the duration columns, averaging them will be
no problem.

Well, I tried changing the data types. When I tried changing it to "Number"
I got the error:

Database encountered errors while converting the data
The Contents of Fields in 603 record(s) were deleted.
Do you want to proceed anyway?

Below is sample data from the field. Anytime I change the data type from
text I get the above error.

AvgACD
:33
2:29
1:50
2:16
1:37
1:44
1:54
:15
2:17

Could you share your data types of the fields? A WAG suggests you are
attempting to Sum a text field.
I am trying to create a query and I am getting an error that states:

[quoted text clipped - 23 lines]
Anyone know what I can do to be able to sum these fields per employee and
program?

 

[John Vinson]

PS, that other dude's replace string is way better since it actually
works if you have a single 0 on the left of the ":"

.... or nothing at all, e.g. the :59 example.

John W. Vinson[MVP]

 

[ladybug via AccessMonster.com]

Ok, now I am completely confused as to which suggestion I should be trying.
I tried an update query (which I am not sure I even did correctly)

When I put in:
60*Val(Left("0" & [AvgACD], InStr("0" & [AvgACD], ":")-1)) +Val(Mid([AvgACD],
InStr([AvgACD], ":") + 1))

I received an error that the expression is invalid. (dot)or ! operator or
invalid parentheses)

If someone can explain this step by step for me I would greatly appreciate it.
Sorry, I'm still learning!

 

[John Vinson]

Ok, now I am completely confused as to which suggestion I should be trying.
I tried an update query (which I am not sure I even did correctly)

When I put in:
60*Val(Left("0" & [AvgACD], InStr("0" & [AvgACD], ":")-1)) +Val(Mid([AvgACD],
InStr([AvgACD], ":") + 1))

I received an error that the expression is invalid. (dot)or ! operator or
invalid parentheses)

Sorry! It's nested pretty deep and it was untested "air code". Let's
describe it from the inside out:

"0" & [AvgACD]

Concatenating a 0 with [AvgACD] will convert ":59" to "0:59", and
"3:12" to "03:12". This will prevent later problems with empty strings
causing the Val() function to choke.

InStr("0" & [AvgACD], ":")

will return the position of the : character in the resulting string -
e.g. if AvgACD is "3:12" it will return 3, since the third byte of
"03:12" is a colon.

Left("0" & [AvgACD], InStr("0" & [AvgACD], ":")-1)

will return the leftmost two bytes of "03:12" - a text string "03". I
subtract 1 from the InStr result so it doesn't bring along the
unneeded colon.

Val(Left("0" & [AvgACD], InStr("0" & [AvgACD], ":")-1))

will convert the text string "03" to an integer number 3.

Val(Mid([AvgACD],InStr([AvgACD], ":") + 1))

does the same thing, but rather than using the Left() function to
return the leftmost bytes, it uses the Mid() function to return the
substring starting one character to the right of the colon.

So this LOOKS correct. Could you please open the actual query in SQL
view and post the entire SQL text to a message here? Also post the
exact wording of the error message.

John W. Vinson[MVP]

 

[ladybug via AccessMonster.com]

SQL view will not open. I get the error:

The expression you entered is invalid. (dot)or ! operator or invalid
parentheses.
You may have enteredan invalid identifieror typed parenthesesfollowing the
Nullconstant.

Ok, now I am completely confused as to which suggestion I should be trying.
I tried an update query (which I am not sure I even did correctly)

[quoted text clipped - 5 lines]

I received an error that the expression is invalid. (dot)or ! operator or
invalid parentheses)

Sorry! It's nested pretty deep and it was untested "air code". Let's
describe it from the inside out:

"0" & [AvgACD]

Concatenating a 0 with [AvgACD] will convert ":59" to "0:59", and
"3:12" to "03:12". This will prevent later problems with empty strings
causing the Val() function to choke.

InStr("0" & [AvgACD], ":")

will return the position of the : character in the resulting string -
e.g. if AvgACD is "3:12" it will return 3, since the third byte of
"03:12" is a colon.

Left("0" & [AvgACD], InStr("0" & [AvgACD], ":")-1)

will return the leftmost two bytes of "03:12" - a text string "03". I
subtract 1 from the InStr result so it doesn't bring along the
unneeded colon.

Val(Left("0" & [AvgACD], InStr("0" & [AvgACD], ":")-1))

will convert the text string "03" to an integer number 3.

Val(Mid([AvgACD],InStr([AvgACD], ":") + 1))

does the same thing, but rather than using the Left() function to
return the leftmost bytes, it uses the Mid() function to return the
substring starting one character to the right of the colon.

So this LOOKS correct. Could you please open the actual query in SQL
view and post the entire SQL text to a message here? Also post the
exact wording of the error message.

John W. Vinson[MVP]

 

[John Vinson]

SQL view will not open. I get the error:

The expression you entered is invalid. (dot)or ! operator or invalid
parentheses.
You may have enteredan invalid identifieror typed parenthesesfollowing the
Nullconstant.

Wierd.

Try rebuilding the query, step by step, from scratch. At what point
does it break down?

John W. Vinson[MVP]

 

[ladybug via AccessMonster.com]

I could be doing something completely wrong, again I am a beginner.

I selected each field and placed it in the field category of the query. I
then pasted the code you gave me into the Criteria field. That is when I get
the error.

 

[John Vinson]

I could be doing something completely wrong, again I am a beginner.

I selected each field and placed it in the field category of the query. I
then pasted the code you gave me into the Criteria field. That is when I get
the error.

That's the problem, then!

Instead:

- Add a new field to your table, of datatype Long Integer.
- Create a new query based on the table.
- Make it an UPDATE query.
- Put the expression in the Update To line under this new field.
- Run the query by clicking the ! icon.

John W. Vinson[MVP]

 

[ladybug via AccessMonster.com]

It worked! Thank you so much for helping me. Access can be such a great
tool, there is just so much to learn.

One last question. Do you have a code that can return it to time format once
I have summed the data?

 

[John Vinson]

One last question. Do you have a code that can return it to time format once
I have summed the data?

ShowTime: Sum([duration]) \ 60 & Format(Sum([duration]) MOD 60, ":00")

John W. Vinson[MVP]