Re: Expression too complex Error

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" <leonard522@xxxxxxx> wrote in message
news:1152792237.427944.212050@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

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

--

Ken Snell
<MS ACCESS MVP>

"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:OAZd0ihpGHA.3564@xxxxxxxxxxxxxxxxxxxxxxx
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
http://I.Am/DougSteele
(no private e-mails, please)


"Jen" <leonard522@xxxxxxx> wrote in message
news:1152752406.708881.325590@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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






.

Relevant Pages

  • Re: Expression too complex Error
    ... Ken Snell ... <MS ACCESS MVP> ... use zero as the result if the divisor is zero: ... FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON ...
    (microsoft.public.access.queries)
  • Re: Expression too complex Error
    ... Ken Snell wrote: ... <MS ACCESS MVP> ... use zero as the result if the divisor is zero: ... FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON ...
    (microsoft.public.access.queries)
  • Re: Visible or Invisible Code
    ... >> <MS ACCESS MVP> ... >>> BE A NUMBER) Is it a text-formatted control? ... >>> you saying that the test for zero should look at ... >> FTimeBillingSub ...
    (microsoft.public.access.forms)
  • Re: Help with calculation in query!!!
    ... Access MVP 2002-2005, 2007-2008 ... There is no zero value for K and N, but L has some decimal and zero in the data. ... Dim varReturn As Variant ...
    (microsoft.public.access.queries)
  • Re: change null count in crosstab qry to zeros Trouble with Nz
    ... I will NEVER see a count of zero. ... Dave Hargis, Microsoft Access MVP ... Null out of the query. ... FROM ErgoIssueTbl RIGHT JOIN ActionItemtbl ON ErgoIssueTbl.IssueNum ...
    (microsoft.public.access.queries)
Loading