I'm trying to generate a report based on the query shown below. When I try to enter a value in my form for the variables Min revenues; Max reveneues; Min investment; Max investment I always get the following error message:
The 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 expressions by assigning parts of the expression to variables.

What is wrong with the query below?

SQL code query:
SELECT tbl_PE.[Company Name], tbl_PE.[Buy and build], tbl_PE.[Early stage], tbl_PE.[Financing / Mezzanine], tbl_PE.Growth, tbl_PE.[Market leaders / stable companies], tbl_PE.[MBO / MBI], tbl_PE.[Restructuring / Distressed], tbl_PE.[Spin-Offs / Carve-Outs], tbl_PE.Succession, tbl_PE.[All industries], tbl_PE.[Construction and materials], tbl_PE.[Financial services], tbl_PE.Healthcare, tbl_PE.[Industrial goods and services], tbl_PE.[Media, technology and telecom], tbl_PE.[Retail and consumer products], tbl_PE.CH, tbl_PE.GSA, tbl_PE.Europe, tbl_PE.Worldwide, tbl_PE.[Min revenues (MCHF)], tbl_PE.[Max reveneues (MCHF)], tbl_PE.[Min investment (MCHF)], tbl_PE.[Max investment (MCHF)], tbl_PE.[Additional comments], tbl_Contacts.[Company Contact Last Name], tbl_Contacts.[Company Contact First Name], tbl_Contacts.[Email Company Contact], tbl_Contacts.[Company Contact Work Phone], tbl_Contacts.[Company Contact Mobile Phone], tbl_Company.[Address Line 1], tbl_Company.[Postal Code], tbl_Company.City, tbl_Company.Country, tbl_Company.[Web Site]
FROM tbl_Company INNER JOIN (tbl_Contacts INNER JOIN tbl_PE ON tbl_Contacts.[Company Contact Last Name] = tbl_PE.[Contact Name]) ON tbl_Company.[Company Name] = tbl_PE.[Company Name]
WHERE (((tbl_PE.[Company Name]) Like [Forms]![frm_PEqry]![CompanyName] & "*") AND ((tbl_PE.[Buy and build]) Like [Forms]![frm_PEqry]![Buyandbuild] & "*") AND ((tbl_PE.[Early stage]) Like [Forms]![frm_PEqry]![Earlystage] & "*") AND ((tbl_PE.[Financing / Mezzanine]) Like [Forms]![frm_PEqry]![Financing] & "*") AND ((tbl_PE.Growth) Like [Forms]![frm_PEqry]![Growth] & "*") AND ((tbl_PE.[Market leaders / stable companies]) Like [Forms]![frm_PEqry]![leaders] & "*") AND ((tbl_PE.[MBO / MBI]) Like [Forms]![frm_PEqry]![MBO] & "*") AND ((tbl_PE.[Restructuring / Distressed]) Like [Forms]![frm_PEqry]![Restructuring] & "*") AND ((tbl_PE.[Spin-Offs / Carve-Outs]) Like [Forms]![frm_PEqry]![Spin_Offs] & "*") AND ((tbl_PE.Succession) Like [Forms]![frm_PEqry]![Succession] & "*") AND ((tbl_PE.[All industries]) Like [Forms]![frm_PEqry]![Allindustries] & "*") AND ((tbl_PE.[Construction and materials]) Like [Forms]![frm_PEqry]![Constructionandmaterials] & "*") AND ((tbl_PE.[Financial services]) Like [Forms]![frm_PEqry]![Financialservices] & "*") AND ((tbl_PE.Healthcare) Like [Forms]![frm_PEqry]![Healthcare] & "*") AND ((tbl_PE.[Industrial goods and services]) Like [Forms]![frm_PEqry]![Industrials] & "*") AND ((tbl_PE.[Media, technology and telecom]) Like [Forms]![frm_PEqry]![MTT] & "*") AND ((tbl_PE.[Retail and consumer products]) Like [Forms]![frm_PEqry]![Retail] & "*") AND ((tbl_PE.CH) Like [Forms]![frm_PEqry]![CH] & "*") AND ((tbl_PE.GSA) Like [Forms]![frm_PEqry]![GSA] & "*") AND ((tbl_PE.Europe) Like [Forms]![frm_PEqry]![Europe] & "*") AND ((tbl_PE.Worldwide) Like [Forms]![frm_PEqry]![Worldwide] & "*") AND ((tbl_PE.[Min revenues (MCHF)])>=[Forms]![frm_PEqry]![Min_revs]) AND ((tbl_PE.[Max reveneues (MCHF)])<=[Forms]![frm_PEqry]![Max_revs]) AND ((tbl_PE.[Min investment (MCHF)])>=[Forms]![frm_PEqry]![Min_investment]) AND ((tbl_PE.[Max investment (MCHF)])<=[Forms]![frm_PEqry]![Max_investment]))
ORDER BY tbl_PE.[Company Name];

Thanks for your help!

A bit self explanitory, try taking out your like conditions and introduce one at a time to discover the query limits.

David

I don't think that I have too many Like conditions. When I use the following code (with more Like conditions), everything works. The thing is, I don't want to use the Like condition for the last variables, but <= or >=
Once I enter these conditions into the code, I'm getting the error message.

SELECT tbl_PE.[Company Name], tbl_PE.[Buy and build], tbl_PE.[Early stage], tbl_PE.[Financing / Mezzanine], tbl_PE.Growth, tbl_PE.[Market leaders / stable companies], tbl_PE.[MBO / MBI], tbl_PE.[Restructuring / Distressed], tbl_PE.[Spin-Offs / Carve-Outs], tbl_PE.Succession, tbl_PE.[All industries], tbl_PE.[Construction and materials], tbl_PE.[Financial services], tbl_PE.Healthcare, tbl_PE.[Industrial goods and services], tbl_PE.[Media, technology and telecom], tbl_PE.[Retail and consumer products], tbl_PE.CH, tbl_PE.GSA, tbl_PE.Europe, tbl_PE.Worldwide, tbl_PE.[Min revenues (MCHF)], tbl_PE.[Max reveneues (MCHF)], tbl_PE.[Min investment (MCHF)], tbl_PE.[Max investment (MCHF)], tbl_PE.[Additional comments], tbl_Contacts.[Company Contact Last Name], tbl_Contacts.[Company Contact First Name], tbl_Contacts.[Email Company Contact], tbl_Contacts.[Company Contact Work Phone], tbl_Contacts.[Company Contact Mobile Phone], tbl_Company.[Address Line 1], tbl_Company.[Postal Code], tbl_Company.City, tbl_Company.Country, tbl_Company.[Web Site]
FROM tbl_Company INNER JOIN (tbl_Contacts INNER JOIN tbl_PE ON tbl_Contacts.[Company Contact Last Name]=tbl_PE.[Contact Name]) ON tbl_Company.[Company Name]=tbl_PE.[Company Name]
WHERE (((tbl_PE.[Company Name]) Like Forms!frm_PEqry!CompanyName & "*") And ((tbl_PE.[Buy and build]) Like Forms!frm_PEqry!Buyandbuild & "*") And ((tbl_PE.[Early stage]) Like Forms!frm_PEqry!Earlystage & "*") And ((tbl_PE.[Financing / Mezzanine]) Like Forms!frm_PEqry!Financing & "*") And ((tbl_PE.Growth) Like Forms!frm_PEqry!Growth & "*") And ((tbl_PE.[Market leaders / stable companies]) Like Forms!frm_PEqry!leaders & "*") And ((tbl_PE.[MBO / MBI]) Like Forms!frm_PEqry!MBO & "*") And ((tbl_PE.[Restructuring / Distressed]) Like Forms!frm_PEqry!Restructuring & "*") And ((tbl_PE.[Spin-Offs / Carve-Outs]) Like Forms!frm_PEqry!Spin_Offs & "*") And ((tbl_PE.Succession) Like Forms!frm_PEqry!Succession & "*") And ((tbl_PE.[All industries]) Like Forms!frm_PEqry!Allindustries & "*") And ((tbl_PE.[Construction and materials]) Like Forms!frm_PEqry!Constructionandmaterials & "*") And ((tbl_PE.[Financial services]) Like Forms!frm_PEqry!Financialservices & "*") And ((tbl_PE.Healthcare) Like Forms!frm_PEqry!Healthcare & "*") And ((tbl_PE.[Industrial goods and services]) Like Forms!frm_PEqry!Industrials & "*") And ((tbl_PE.[Media, technology and telecom]) Like Forms!frm_PEqry!MTT & "*") And ((tbl_PE.[Retail and consumer products]) Like Forms!frm_PEqry!Retail & "*") And ((tbl_PE.CH) Like Forms!frm_PEqry!CH & "*") And ((tbl_PE.GSA) Like Forms!frm_PEqry!GSA & "*") And ((tbl_PE.Europe) Like Forms!frm_PEqry!Europe & "*") And ((tbl_PE.Worldwide) Like Forms!frm_PEqry!Worldwide & "*") And ((tbl_PE.[Min revenues (MCHF)]) Like Forms!frm_PEqry!Min_revs & "*") And ((tbl_PE.[Max reveneues (MCHF)]) Like Forms!frm_PEqry!Max_revs & "*") And ((tbl_PE.[Min investment (MCHF)]) Like Forms!frm_PEqry!Min_investment & "*") And ((tbl_PE.[Max investment (MCHF)]) Like Forms!frm_PEqry!Max_investment & "*"))
ORDER BY tbl_PE.[Company Name];

Placing everything on one line implies an AND operator unless you specify otherwise. Try putting your OR statement on the next line down.

David

Unfortunately does not solve the problem...

I've tried this version, but still returns the same error

FROM tbl_Company INNER JOIN (tbl_Contacts INNER JOIN tbl_PE ON tbl_Contacts.[Company Contact Last Name]=tbl_PE.[Contact Name]) ON tbl_Company.[Company Name]=tbl_PE.[Company Name]
WHERE (((tbl_PE.[Company Name]) Like Forms!frm_PEqry!CompanyName & "*") And ((tbl_PE.) Like Forms!frm_PEqry!Buyandbuild & "*") And ((tbl_PE.[Early stage]) Like Forms!frm_PEqry!Earlystage & "*") And ((tbl_PE.[Financing / Mezzanine]) Like Forms!frm_PEqry!Financing & "*") And ((tbl_PE.Growth) Like Forms!frm_PEqry!Growth & "*") And ((tbl_PE.[Market leaders / stable companies]) Like Forms!frm_PEqry!leaders & "*") And ((tbl_PE.[MBO / MBI]) Like Forms!frm_PEqry!MBO & "*") And ((tbl_PE.[Restructuring / Distressed]) Like Forms!frm_PEqry!Restructuring & "*") And ((tbl_PE.[Spin-Offs / Carve-Outs]) Like Forms!frm_PEqry!Spin_Offs & "*") And ((tbl_PE.Succession) Like Forms!frm_PEqry!Succession & "*") And ((tbl_PE.[All industries]) Like Forms!frm_PEqry!Allindustries & "*") And ((tbl_PE.[Construction and materials]) Like Forms!frm_PEqry!Constructionandmaterials & "*") And ((tbl_PE.[Financial services]) Like Forms!frm_PEqry!Financialservices & "*") And ((tbl_PE.Healthcare) Like Forms!frm_PEqry!Healthcare & "*") And ((tbl_PE.[Industrial goods and services]) Like Forms!frm_PEqry!Industrials & "*") And ((tbl_PE.[Media, technology and telecom]) Like Forms!frm_PEqry!MTT & "*") And ((tbl_PE.[Retail and consumer products]) Like Forms!frm_PEqry!Retail & "*") And ((tbl_PE.CH) Like Forms!frm_PEqry!CH & "*") And ((tbl_PE.GSA) Like Forms!frm_PEqry!GSA & "*") And ((tbl_PE.Europe) Like Forms!frm_PEqry!Europe & "*") And ((tbl_PE.Worldwide) Like Forms!frm_PEqry!Worldwide & "*") And [B](tbl_PE.[Min revenues (MCHF)]>=Nz(Forms!frm_PEqry!Min_revs,0)) And (tbl_PE.[Min investment (MCHF)]>=Nz(Forms!frm_PEqry!Min_investment,0)))

That's a lot of criteria - I broke them up to look at them properly:

WHERE (
((tbl_PE.[Company Name]) Like [Forms]![frm_PEqry]![CompanyName] & "*") AND
((tbl_PE.[Buy and build]) Like [Forms]![frm_PEqry]![Buyandbuild] & "*") AND
((tbl_PE.[Early stage]) Like [Forms]![frm_PEqry]![Earlystage] & "*") AND
((tbl_PE.[Financing / Mezzanine]) Like [Forms]![frm_PEqry]![Financing] & "*") AND
((tbl_PE.Growth) Like [Forms]![frm_PEqry]![Growth] & "*") AND
((tbl_PE.[Market leaders / stable companies]) Like [Forms]![frm_PEqry]![leaders] & "*") AND
((tbl_PE.[MBO / MBI]) Like [Forms]![frm_PEqry]![MBO] & "*") AND
((tbl_PE.[Restructuring / Distressed]) Like [Forms]![frm_PEqry]![Restructuring] & "*") AND
((tbl_PE.[Spin-Offs / Carve-Outs]) Like [Forms]![frm_PEqry]![Spin_Offs] & "*") AND
((tbl_PE.Succession) Like [Forms]![frm_PEqry]![Succession] & "*") AND
((tbl_PE.[All industries]) Like [Forms]![frm_PEqry]![Allindustries] & "*") AND
((tbl_PE.[Construction and materials]) Like [Forms]![frm_PEqry]![Constructionandmaterials] & "*") AND
((tbl_PE.[Financial services]) Like [Forms]![frm_PEqry]![Financialservices] & "*") AND
((tbl_PE.Healthcare) Like [Forms]![frm_PEqry]![Healthcare] & "*") AND
((tbl_PE.[Industrial goods and services]) Like [Forms]![frm_PEqry]![Industrials] & "*") AND
((tbl_PE.[Media, technology and telecom]) Like [Forms]![frm_PEqry]![MTT] & "*") AND
((tbl_PE.[Retail and consumer products]) Like [Forms]![frm_PEqry]![Retail] & "*") AND
((tbl_PE.CH) Like [Forms]![frm_PEqry]![CH] & "*") AND
((tbl_PE.GSA) Like [Forms]![frm_PEqry]![GSA] & "*") AND
((tbl_PE.Europe) Like [Forms]![frm_PEqry]![Europe] & "*") AND
((tbl_PE.Worldwide) Like [Forms]![frm_PEqry]![Worldwide] & "*") AND
((tbl_PE.[Min revenues (MCHF)])>=[Forms]![frm_PEqry]![Min_revs]) AND
((tbl_PE.[Max reveneues (MCHF)])<=[Forms]![frm_PEqry]![Max_revs]) AND
((tbl_PE.[Min investment (MCHF)])>=[Forms]![frm_PEqry]![Min_investment]) AND
((tbl_PE.[Max investment (MCHF)])<=[Forms]![frm_PEqry]![Max_investment])
)

Is this a saved query? If so, you might be able to break it into two parts - the first one selects all the records that match the first half of the criteria, the second query (now based on the first) picks out those records that also match the second half of the criteria - that's an AND conjunction stll.

If the the values you're testing are discrete quantities, you can change Where X>=Y into Where X>Y-[your smallest discrete increment]

So if they're integer values:
Where X>=Y
is the same as:
Where X>(Y-1)

If they're currency (i.e. hundredths):
Where X>=Y
is the same as:
Where X>(Y-0.01)

Not sure if this would make it simpler to evaluate though.

I've tried only using < instead of <=
It did not solve the problem

My feeling is, the problem has nothing to do with the number of criteria. When I use "Like" instead of <= or >= the query works fine