'Expression is typed incorrectly, or it is too complex to be evaluated'

[dwheatley]

Hi,



I am designing a query which will return results based on user selections in a form.

The user can choose from Company, Service, Number of Issues, and Category, choosing from as many or as few of these options as they wish to search by. The user also enters a number which is the greatest price they wish to see.

The results are also filtered by dates after today's date automatically.

The query runs upon the click of a button in the search form.

The SQL of the query is here:

SELECT tblCompanies.Company, tblServicePrice.Service, tblServicePrice.[Number of Issues], tblCompanies.Category, tblServicePrice.[Price in £], tblDeadlines.Deadline, tblDeadlines.Issue, tblServicePrice.[Original Price]
FROM (tblCompanies INNER JOIN tblServicePrice ON tblCompanies.ID = tblServicePrice.Company) INNER JOIN tblDeadlines ON tblCompanies.ID = tblDeadlines.Company
WHERE (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND ((tblCompanies.Category.Value)=[Forms]![SearchF]![Category])) OR (((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND ((tblCompanies.Category.Value)=[Forms]![SearchF]![Category]) AND (([Forms]![SearchF]![Service]) Is Null)) OR (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND ((tblCompanies.Category.Value)=[Forms]![SearchF]![Category]) AND (([Forms]![SearchF]![Number of Issues]) Is Null)) OR (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblCompanies.Category.Value)=[Forms]![SearchF]![Category]) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND (([Forms]![SearchF]![Category]) Is Null)) OR (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND (([Forms]![SearchF]![Company]) Is Null) AND (([Forms]![SearchF]![Category]) Is Null)) OR (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND (([Forms]![SearchF]![Number of Issues]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null) AND (([Forms]![SearchF]![Category]) Is Null)) OR (((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblCompanies.Category.Value)=[Forms]![SearchF]![Category]) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Number of Issues]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND ((tblCompanies.Category.Value)=[Forms]![SearchF]![Category]) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Number of Issues]) Is Null)) OR (((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblCompanies.Category.Value)=[Forms]![SearchF]![Category]) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND (([Forms]![SearchF]![Number of Issues]) Is Null) AND (([Forms]![SearchF]![Category]) Is Null)) OR (((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Number of Issues]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null) AND (([Forms]![SearchF]![Category]) Is Null)) OR (((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Category]) Is Null)) OR (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblCompanies.Category.Value)=[Forms]![SearchF]![Category]) AND (([Forms]![SearchF]![Number of Issues]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Number of Issues]) Is Null) AND (([Forms]![SearchF]![Category]) Is Null)) OR (((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblServicePrice.[Price in £])<=[Forms]![SearchF]![Price in £]) AND ((tblDeadlines.Deadline)>Date()) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null) AND (([Forms]![SearchF]![Category]) Is Null));

I am not sure why I am receiving the error. It happened since I started to also make it possible to search by 'Category'. Does introducing a fourth searchable variable into the form make the query too complex to run?

Thanks!

[Minty]

Holy massive query Batman...

I would consider breaking that down into your main query and then base your amounts filtering on that as a second stage.
The pricing criteria are making that very complicated.

[dwheatley]

Yeah it does seem pretty big...I basically just want it so that if a user leaves any combination of fields null then the query will still return the appropriate results, and when there are four separate fields to be left blank or not and any combination of them left blank or not, it means that I have a very long query. Not sure if I am doing this in the most efficient way...

What do you mean base my amounts filtering as a second stage?

I am surprised that the pricing criteria is the problem - it seems pretty simple for the query to just only return results lower than the price the user enters?

Thanks for your help!

[Minty]

I would remove the criteria one at a time until it runs. Also make sure the fields you are using as criteria have indexes on them.
Once it's running save that query, then create another query based on that one and add one set of the removed criteria back in, rinse and repeat.

[CJ_London]

looks to me like you have made it a lot more complicated than it needs to be providing all the permutation. Perhaps that is the way it needs to be and I'm not going through the whole thing, but have you considered criteria along the lines of

AND (tblCompanies.Category.Value=[Forms]![SearchF]![Category] OR [Forms]![SearchF]![Category] is Null)

AND (tblServicePrice.Company=[Forms]![SearchF]![Company] OR =[Forms]![SearchF]![Company] is Null)

etc

basically making better use of AND and OR

[sgthuth]

I agree with Ajax. Readability is not only important for you when troubleshooting but important for others trying to see and understand what you are doing.

[dwheatley]

I appreciate that - the reason I have done it like this so far is that this is the only way that I know how to. I have now isolated the initial problem and resolved it.

The way I currently tell the query what to do in any particular situation is by adding every possible combination of X or X Is Null to the criteria section in query design (see attachment).

This is quite tedious and means once I get to more than three aspects which can either be Null or not there are a lot of combinations to include.

What is an easier way to do this?

Thanks for your help.

[Minty]

As the others have suggested , and sort of in line with what I suggested;
Your Deadline and Price criteria apply to the whole query.

If you applied them to an initial query, a) your resulting recordset would be significantly smaller b) the second query would become much easier to read, and perform much quicker as it's only applied to a pre-filtered recordset.

[CJ_London]

What is an easier way to do this?

see post #5

[Minty]

see post #5

If you build a saved query using your syntax and the open it in the query editor - you end up with the mess as per the OP's picture unfortunately.