在VBA中的UPDATE查询出现问题 [英] Trouble with UPDATE query in VBA

问题描述

我正在尝试创建一个全选复选框使用更新查询，该查询使用与查询相同的条件，该查询是我正在查看的子表单的数据源。但是，它工作得不对。如果条件中的四个控件中的任何一个为null，则会收到一条错误消息，指出无效使用Null。如果所有四个控件都包含其中的某些内容，则会收到一条错误消息，指出参数太少。预期1.我不确定有什么问题。我最初复制并粘贴了查询的标准，该查询是子表单的数据源（无论是否填充了搜索控件），然后缩短代码，我使用了Replace函数。没有变化。


这是代码：

I''m trying to create a "Select All" check box using an update query that uses the same criteria as the query that is the data source for the subform I''m viewing. However, it isn''t working quite right. If any of the four controls in the criteria are null, then I get an error message that says "invalid use of Null". If all four of the controls have something in them, then I get an error message that says "Too few parameters. Expected 1." I''m not sure what is wrong. I had originally copied and pasted the criteria out of the query that is the data source for the subform (that works whether or not the search controls are populated) and then to shorten the code, I used the Replace function. No change.

Here is the code:

< span class =codeLinkonclick =Blur（this，this.parentNode.parentNode，getChildren（this），true）;>展开 | 选择 | Wrap | 行号

推荐答案

Seth，


我知道我注意到的一件事是在WHERE语句的第一部分。如果Not IsNull（％B）计算结果为False，您的Where语句将以WHERE'*''AND开头。您的其他IIF语句也是如此。


最好构建首先评估控件值的字符串。然后，根据该评估，将文本添加到字符串。例如，您将拥有：

Seth,

I know that one thing i notice is in the first part of your WHERE statement. If "Not IsNull(%B)" evaluates to False, your Where statement would begin with "WHERE ''*'' AND ". The same goes for your other IIF statements.

Better to build the string in which you evaluate the value of the control first. Then, based on that evaluation, add text to the string. For example, you would have:

展开 | 选择 | Wrap | 行号

您最终会得到无效的SQL语法即使您修复了错误。你得到null的无效使用，因为替换函数的第三个参数不能使用null。


当你得到你的时候，你不知道哪一行突出显示其他错误，所以我甚至无法开始猜测。

You''re going to end up with invalid SQL syntax even if you fix the errors. You''re getting invalid use of null because the third parameter of the replace function can''t use null.

You don''t say which line is highlighted when you get your other error so I can''t even begin to guess.

第14行是当我收到其他错误时突出显示的行。


从我的iPhone

Line 14 is the line that gets highlighted when I get the other error.

From my iPhone

这篇关于在VBA中的UPDATE查询出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！