Too complex expression

[honeypot3]

Hi there

I wonder if you can help me, the following SQL works with no trouble at all:

SELECT Trade.Trade, CDate([year] & "-" & [month] & "-01") AS CalcDate, (Avg([Resp])*8)/80 AS [Percent]
FROM (Trade INNER JOIN ContractorDetails ON Trade.TradeID = ContractorDetails.TradeID) INNER JOIN ContJobQueResMonYearLink ON ContractorDetails.[Contractor ID] = ContJobQueResMonYearLink.CoID
GROUP BY Trade.Trade, CDate([year] & "-" & [month] & "-01")
HAVING (((CDate([year] & "-" & [month] & "-01"))>=CDate([forms]![dateselect3]![year] & "-" & [forms]![dateselect3]![month] & "-01") And (CDate([year] & "-" & [month] & "-01"))<=CDate([forms]![dateselect3]![toyear] & "-" & [forms]![dateselect3]![tomonth] & "-01")))
ORDER BY Trade.Trade;


But when I try to extend this to include another field which belongs in another query called PartnershipName Access tells me that the expression is too complex (please see below) - any ideas why?

SELECT [NewExcelOutputPartner contract subquery].[Partnership Name], Trade.Trade, CDate([year] & "-" & [month] & "-01") AS CalcDate, Avg([resp])*8/80 AS [Percentage Average]
FROM ([NewExcelOutputPartner contract subquery] INNER JOIN ContractorDetails ON [NewExcelOutputPartner contract subquery].CoID=ContractorDetails.[Contractor ID]) INNER JOIN Trade ON ContractorDetails.TradeID=Trade.TradeID
GROUP BY [NewExcelOutputPartner contract subquery].[Partnership Name], Trade.Trade, CDate([year] & "-" & [month] & "-01")
HAVING (((CDate([year] & "-" & [month] & "-01"))>=CDate(forms!dateselect3!year & "-" & forms!dateselect3!month & "-01") And (CDate([year] & "-" & [month] & "-01"))<=CDate(forms!dateselect3!toyear & "-" & forms!dateselect3!tomonth & "-01")))
ORDER BY [NewExcelOutputPartner contract subquery].[Partnership Name], Trade.Trade;

 

[Crowley16]

maybe because the query is too complex, there's only a certain number of tables you can call in a query, and referencing another subquery doesn't count as 1 table, rather it counts as all the tables referenced in the subquery.

--------------------
Procrastinate Now!

 

[honeypot3]

I don't think it is the number of tables/queries as it works fine if you just remove "))>=CDate(forms!dateselect3!year & "-" & forms!dateselect3!month & "-01") And (CDate([year] & "-" & [month] & "-01"))<=CDate(forms!dateselect3!toyear & "-" & forms!dateselect3!tomonth & "-01")))

It seems to be that once you try to run the query where you are setting the criteria of selecting the date range that the query struggles - it works fine with all the tables/queries and Cdate initial field but falls over when it is asked to look at the entries on the form DateSelect3.

Any other ideas?

 

[lespaul]

you don't want that in a HAVING clause anyway, move it to a WHERE clause.

may not solve your problem, but it's the correct syntax.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[honeypot3]

Solved it - got the smaller version of the query to make a table of its own first then ran the form selection criteria separtely - thanks for your efforts though