Expression too complex error

[seenfresh]

Hi,

I have a problem where I keep receiving a message after I run a query that states .... expression is type incorrectly, or is too complex...

The expression comes from one of my query's (Query1) and in this query I have four tables with INNER JOIN relationships set up excluding various other fields and expressions I have in this query the field which gives me the problem is this expression:

Site_Net_Rev_P: (([Affiliate Detail Report]![Affiliate_Delivery]/[Affiliate Detail Report]![Line_Item_Delivery])*[Consolidated_BILLING EXPORT_Table]![Net Invoice Amount])*[Live Stations Detail]![National_Ad Split Rate]

To help clarify my issue... when I run (Query1) everthing is fine and the expression above along with other expressions calculate perfectly. However, I have another query that uses Query1 to obtain information from the field where the expression above exists. This Query (Query2) uses a LEFT OUTER JOIN from one query into Query1 to find matching records and pull the information from the field where the expression above pertains to. When I run this Query (Query2) I keep receiving the error message stated above.

I really stuck on this one, I'm not sure the best way to debug this issue and/or try to minimize the complexity of the expression (although it does not seem complex at all ?)

Any suggestions would be a life saver!

Thanks in advance,

 

[apr pillai]

Check whether the [Affiliate Detail Report]![Line_Item_Delivery] field is ending up with Zero or Null value in Query2 (when you join with other table/query) if so a Division by Zero Error can occur on any of those records.

As a debugging exercise replace this particular field with a constant value in the expression for the time being and insert this field alone in another column and check whether in any of the record in the new column is ending up with Zero or Null Value. If so you have to take appropriate steps to correct this condition before you are able to run this Query with your expression.

 

[seenfresh]

Thanks for your help

I ran the query and looked for any null values or zero's in the Line Item Delivery field by itself. There are no null values or zero's evident. I also check the main Data Table where this field resides and no null's or zero's evident. I did notice in the [Affiliate Detail Table]![Affiliate Delivery] field their are zero's evident. When I run Query2 with the expression in the previous post their are no null values evident. Seems like Query2 runs fine, but for some reason I cannot query it using another query... ummm

Any thought's?

Thanks again!