“表达太复杂”有多复杂？错误3075 [英] How complex is “Expression too complex”? error 3075

问题描述

MSA 2007，错误3075：查询表达式中的表达式太复杂

我有最终用户搜索表单。我在"包含："和"但不包含："形式的顶部有两个字段。用户键入条件，我的VB从中生成SQL ，如下所示：

包含：Foo1，Foo2，Foo3

但不包含：Bar1，Bar2，Bar3

它出现了：

（（（（（[SearchField]喜欢"* Foo1 *"）或[SearchField]喜欢"* Foo2 *"）或[SearchField]喜欢"* Foo2 *"） AND [SearchField]不喜欢"* Bar1 *"）和[SearchField]不喜欢"* Bar2 *"）和[SearchField]不喜欢"* Bar3 *"）

- I我觉得我在那里得到了所有的标点符号，关键是这部分是有效的。

问题是用户有时会提出太多标准（或者是太多，或者是它太长了？）和Access崩溃错误3075.

我需要捕获此错误并引导用户远离问题。

所以我的问题是：微软访问"太复杂"了什么？

是否有我可以在查询的一列中引用的最大字段数？

括号中的最大数量？ 

Ands和Ors的最大数量？

只是条件字符串的最大长度？

以上所有？ 我应该测试所有这些吗？

目前，当用户输入31个单独的标准时，系统崩溃了（你可以告诉它）通过计算左括号），生成的SQL条件字符串长度为2260个字符。

Aha tia

roger

解决方案

您没有提到您正在使用的Access版本。

我'用户输入的过滤条件遇到了类似的问题。

在2007年之前的版本中，过滤器的最大长度是2048.我怀疑它已经改变了，但你可以通过一些实验轻松确认检查生成错误的长度并减少直到没有错误。

您可以在应用之前检查过滤字符串的长度，并通知用户他们输入了太多标准。

MSA 2007, Error 3075: Expression too Complex in query expression

I have and end-user search form. I have two fields at the top of the form "Contains:" and "But does not contain:" The user types in the criteria, and my VB  generates  SQL  from it like this:

Contains: Foo1 ,Foo2, Foo3

But does not contain: Bar1, Bar2, Bar3

And it comes up with:

(((((([SearchField] Like "*Foo1*") OR [SearchField] Like "*Foo2*") OR [SearchField] Like "*Foo2*") AND [SearchField] NOT Like "*Bar1*") AND [SearchField] NOT Like "*Bar2*") AND [SearchField] NOT Like "*Bar3*")

-- I think I got all the punctuation right in there, the point is that part works.

The problem is that the user sometimes puts in too many criteria (or is it too much, or is it too long?) and Access crashes with the error 3075.

I need to trap this error and guide the user away from the problem.

So my question is: what is "too complex" for Microsoft access?

Is there a maximum number of fields I can refer to in one column of a query?

A maximum number of parenthetical statements? 

A maximum number of Ands and Ors?

Just a maximum length to the criteria string?

All of the above?  Should I be testing for all of these?

Currently the system is crashing when the user has entered 31 individual criteria, (which you can tell by counting the opening parentheses) and the SQL criteria string generated is 2260 characters long.

Aha tia

roger

解决方案

You don't mention what version of Access you are using.

I've run into similar problems with user entered filter criteria.
In versions prior to 2007, the max length for a filter was 2048. I doubt that it has changed, but you can easily confirm with some experimentation by checking the length that generates an error and reducing until there is no error.
You can then check the length of your filter string before applying it and notify the user that they entered too much criteria.

这篇关于“表达太复杂”有多复杂？错误3075的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！