查询结果未显示具有空值的记录 [英] Query results not showing records with null values

问题描述

我正在设计一个搜索表单，用于基于4个文本字段(名字，姓氏，电话号码和促销代码)搜索访问数据库.

I'm designing a search form to search my access database based on 4 text fields, Firstname, Surname, Phonenumber and Promo Code.

查询如下:

Like "*" & [Forms]![SearchF]![txtFirstName] & "*"
Like "*" & [Forms]![SearchF]![txtSurnameName] & "*"
Like "*" & [Forms]![SearchF]![txtPhone] & "*"
Like "*" & [Forms]![SearchF]![txtPromo] & "*"

如果所有字段均为空白，则搜索应返回所有记录.在文本框中输入一些值时，结果将缩小到匹配的范围.

The search should return all records if all the fields are blank. When you enter some values in the text boxes, the results are narrowed down to matches.

几乎可以正常工作，但是当某些字段中没有值时，就会出现问题.例如，如果某人没有促销代码，那么即使您将其保留为空白，他们的记录也会始终被跳过.我认为访问查询不喜欢空白字段.

It almost works fine, but problems occur when there is no value in certain fields. For instance, if someone doesn't have a promo code then their record will always be skipped over even if you leave it blank. I assume the access query doesn't like blank fields.

解决这个问题的最简单方法是什么?

What's the easiest way to get around this?

推荐答案

我认为这里的文本框可能会让人分心，因为这个问题更为基本.考虑一下这个查询...

I think the text boxes may be a bit of a distraction here, because the issue is more basic. Consider this query ...

SELECT f.id, f.some_text
FROM tblFoo AS f
WHERE f.some_text Like "*";

它返回其中some_text包含 any 非空值(甚至是空字符串)的行.但是，它不会返回some_text为Null的行.

It returns rows where some_text contains any non-Null value, even an empty string. However it will not return rows where some_text is Null.

原因是Like比较中的Null永远不会评估为True.从立即窗口中看这个例子...

The reason is that Null in a Like comparison will never evaluate as True. Consider this example from the Immediate window ...

? "foo" Like "*"
True
? Null Like "*"
Null

对于您的Like比较，我认为您想要目标字段包含文本框文本的行.但是，当文本框为Null时，返回所有行.您可以将这两个条件与OR组合在一起.

For your Like comparisons, I think you want the rows where the target field contains the text box text. But return all rows when the text box is Null. You can do that by combining those two conditions with OR.

WHERE
    [Promo Code] Like "*" & Forms!SearchF!txtPromo & "*"
    OR
    Forms!SearchF!txtPromo Is Null

当文本框为Null时，OR的第二部分对于所有行均为true，因此将返回所有行.

When the text box is Null, the second part of the OR is true for all rows, so all rows are returned.

当文本框不是Null时，仅返回满足Like比较的那些行.

When the text box is not Null, only those rows which satisfy the Like comparison are returned.

这篇关于查询结果未显示具有空值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！