Expression too complex Error

J

Jen

Hi,
I have the following SQL query, and I am getting the error message:
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.

My query is:

SELECT
Avg((T1.Param71-E1.Param71)/(E1.Param71)*100)-Avg((T2.Param71-E2.Param71)/(E2.Param71)*100)
AS [Ave1],
Avg((T1.Param80-E1.Param80)/(E1.Param80)*100)-Avg((T2.Param80-E2.Param80)/(E2.Param80)*100)
AS [Ave2]
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime
WHERE (((T1.DataCaptureTime) Between [Start Date 2] And [Start Date])
AND ((T2.DataCaptureTime) Between [End Date 2] And [End Date]));

Any ideas?
Thanks so much!
Jen
 
D

Douglas J. Steele

I believe it's complaining about

FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime

There's no linkage between the first part and the second part, so you're
going to end up with a cartesian product between two sub queries. Is that
what you want?
 
K

Ken Snell \(MVP\)

It also could be the result of any of the divisor fields having a zero
value....

--

Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
I believe it's complaining about

FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime

There's no linkage between the first part and the second part, so you're
going to end up with a cartesian product between two sub queries. Is that
what you want?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jen said:
Hi,
I have the following SQL query, and I am getting the error message:
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.

My query is:

SELECT
Avg((T1.Param71-E1.Param71)/(E1.Param71)*100)-Avg((T2.Param71-E2.Param71)/(E2.Param71)*100)
AS [Ave1],
Avg((T1.Param80-E1.Param80)/(E1.Param80)*100)-Avg((T2.Param80-E2.Param80)/(E2.Param80)*100)
AS [Ave2]
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime
WHERE (((T1.DataCaptureTime) Between [Start Date 2] And [Start Date])
AND ((T2.DataCaptureTime) Between [End Date 2] And [End Date]));

Any ideas?
Thanks so much!
Jen
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Oh, I forgot that this error also occurs if the date that is entered as a
parameter value is incomplete or invalid.

--

Ken Snell
<MS ACCESS MVP>

Ken Snell (MVP) said:
It also could be the result of any of the divisor fields having a zero
value....

--

Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
I believe it's complaining about

FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime

There's no linkage between the first part and the second part, so you're
going to end up with a cartesian product between two sub queries. Is that
what you want?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jen said:
Hi,
I have the following SQL query, and I am getting the error message:
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.

My query is:

SELECT
Avg((T1.Param71-E1.Param71)/(E1.Param71)*100)-Avg((T2.Param71-E2.Param71)/(E2.Param71)*100)
AS [Ave1],
Avg((T1.Param80-E1.Param80)/(E1.Param80)*100)-Avg((T2.Param80-E2.Param80)/(E2.Param80)*100)
AS [Ave2]
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime
WHERE (((T1.DataCaptureTime) Between [Start Date 2] And [Start Date])
AND ((T2.DataCaptureTime) Between [End Date 2] And [End Date]));

Any ideas?
Thanks so much!
Jen
Click to expand...
Click to expand...
Click to expand...
 
J

Jen

Thank you! It is from the divisor having a zero value...Is there a way
to use an if statement for this inside the Avg function?
Thanks again!
Jen
It also could be the result of any of the divisor fields having a zero
value....

--

Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
I believe it's complaining about

FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime

There's no linkage between the first part and the second part, so you're
going to end up with a cartesian product between two sub queries. Is that
what you want?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jen said:
Hi,
I have the following SQL query, and I am getting the error message:
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.

My query is:

SELECT
Avg((T1.Param71-E1.Param71)/(E1.Param71)*100)-Avg((T2.Param71-E2.Param71)/(E2.Param71)*100)
AS [Ave1],
Avg((T1.Param80-E1.Param80)/(E1.Param80)*100)-Avg((T2.Param80-E2.Param80)/(E2.Param80)*100)
AS [Ave2]
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime
WHERE (((T1.DataCaptureTime) Between [Start Date 2] And [Start Date])
AND ((T2.DataCaptureTime) Between [End Date 2] And [End Date]));

Any ideas?
Thanks so much!
Jen
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

You'll need to decide what number to use in your average when the divisor is
zero. This is an example of how to set up the expression if you want to use
zero as the result if the divisor is zero:


Avg((T1.Param71-E1.Param71)/(E1.Param71)

becomes

Avg(IIf(E1.Param71=0,0,(T1.Param71-E1.Param71)/(E1.Param71))

--

Ken Snell
<MS ACCESS MVP>


Jen said:
Thank you! It is from the divisor having a zero value...Is there a way
to use an if statement for this inside the Avg function?
Thanks again!
Jen
It also could be the result of any of the divisor fields having a zero
value....

--

Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
I believe it's complaining about

FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime

There's no linkage between the first part and the second part, so
you're
going to end up with a cartesian product between two sub queries. Is
that
what you want?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,
I have the following SQL query, and I am getting the error message:
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.

My query is:

SELECT
Avg((T1.Param71-E1.Param71)/(E1.Param71)*100)-Avg((T2.Param71-E2.Param71)/(E2.Param71)*100)
AS [Ave1],
Avg((T1.Param80-E1.Param80)/(E1.Param80)*100)-Avg((T2.Param80-E2.Param80)/(E2.Param80)*100)
AS [Ave2]
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime
WHERE (((T1.DataCaptureTime) Between [Start Date 2] And [Start Date])
AND ((T2.DataCaptureTime) Between [End Date 2] And [End Date]));

Any ideas?
Thanks so much!
Jen
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Typo:

Avg((T1.Param71-E1.Param71)/(E1.Param71))

becomes

Avg(IIf(E1.Param71=0,0,(T1.Param71-E1.Param71)/(E1.Param71)))


--

Ken Snell
<MS ACCESS MVP>

Ken Snell (MVP) said:
You'll need to decide what number to use in your average when the divisor
is zero. This is an example of how to set up the expression if you want to
use zero as the result if the divisor is zero:


Avg((T1.Param71-E1.Param71)/(E1.Param71)

becomes

Avg(IIf(E1.Param71=0,0,(T1.Param71-E1.Param71)/(E1.Param71))

--

Ken Snell
<MS ACCESS MVP>


Jen said:
Thank you! It is from the divisor having a zero value...Is there a way
to use an if statement for this inside the Avg function?
Thanks again!
Jen
It also could be the result of any of the divisor fields having a zero
value....

--

Ken Snell
<MS ACCESS MVP>

I believe it's complaining about

FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime

There's no linkage between the first part and the second part, so
you're
going to end up with a cartesian product between two sub queries. Is
that
what you want?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,
I have the following SQL query, and I am getting the error message:
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.

My query is:

SELECT
Avg((T1.Param71-E1.Param71)/(E1.Param71)*100)-Avg((T2.Param71-E2.Param71)/(E2.Param71)*100)
AS [Ave1],
Avg((T1.Param80-E1.Param80)/(E1.Param80)*100)-Avg((T2.Param80-E2.Param80)/(E2.Param80)*100)
AS [Ave2]
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON
T1.DataCaptureTime=E1.DataCaptureTime, TestTable AS T2 INNER JOIN
ActualTable AS E2 ON T2.DataCaptureTime=E2.DataCaptureTime
WHERE (((T1.DataCaptureTime) Between [Start Date 2] And [Start Date])
AND ((T2.DataCaptureTime) Between [End Date 2] And [End Date]));

Any ideas?
Thanks so much!
Jen
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with SQL Query - Conditional SHOW 5
crosstab query doesn't recognize expression in source query 4
aggregate function error 1
The expression is typed incorrectly or it is too complex to be evaluated 4
Complex Subquery: ... FROM (TRANSFORM.... Syntax error 2
Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks) 7
Query/Report Help for Date Difference 8
Create a Query that Prompts for Date and Time 4

Top