MS Access具有空字段的多字段搜索 [英] MS Access multi field search with empty fields

问题描述

我有一个非常问题，类似于

I have a problem very similar to this one, but I just can't seem to solve it!

在MS Access(2003)中，我想基于许多字段中的条目搜索表，其中某些字段可能为空.

In MS Access (2003), I want to search a table based on entries in a number of fields, some of which may be empty.

我有:

• 文本字段
• 日期字段
• 整数字段和
• 一个备忘字段(但是，如果很困难，我们可能不必费心搜索该字段).

它们准确地映射到一张桌子上.

They map onto a table exactly.

我正在尝试创建一个查询，当将数据输入到这些字段中的一个或多个时，该查询将返回匹配的行，但是某些字段可以保留为空白.我该怎么办?

I am trying to create a query that will return matching rows when data is entered into one or more of these fields, but some fields can be left blank. How the heck do I do this?

与链接的问题类似的查询适用于文本字段，但是我该如何处理数字字段，日期字段(甚至可能是备忘录字段)?

A query like the one on the linked question works for text fields, but what do I do about the number fields, date fields (and possibly even the memo field)?

举个清楚的例子，以下代码块适用于TextField1，但不适用于NumberField1:

To give a clear example, the following code block works for TextField1, but not NumberField1:

PARAMETERS [Forms]![SearchForm]![FilterTextField1] Text ( 255 ), [Forms]![SearchForm]![FilterNumberField1] Text ( 255 );
SELECT Table1.[TextField1], Table1.[NumberField1], Table1.[TextField2], Table1.[TextField3], Table1.[DateField1], Table1.[DateField2], Table1.[DateField3]
FROM Table1
WHERE (Len([Forms]![SearchForm]![FilterTextField1] & '')=0 OR Table1.[TextField1] Like '*' & [Forms]![SearchForm]![FilterTextField1] & '*') AND (Len([Forms]![SearchForm]![FilterNumberField1] & '')=0 OR Table1.[NumberField1] Like '*' & [Forms]![SearchForm]![FilterNumberField1] & '*');

我希望您能提供帮助.我确定我确实缺少一些明显的东西，但是由于某种原因，我的大脑此刻感觉好像是从耳朵里漏出来的.

I do hope you can help. I'm sure I'm missing something really obvious, but for some reason my brain feels like it is leaking out of my ears at the moment.

谢谢！

如果需要，这是相关实体的基本设计:

If you need it, this is the basic design of the relevant entities:

• SomePrimaryKeyWeDontCareAboutRightNow
• TextField1
• TextField2
• TextField3
• NumberField1
• DateField1
• DateField2
• DateField3
• MemoField1

• FilterTextField1
• FilterTextField2
• FilterTextField3
• FilterNumberField1
• FilterDateField1
• FilterDateField2
• FilterDateField3
• FilterMemoField1

推荐答案

您可以检查null值或将其强制转换为字符串

You can check fo null values or cast to string

这篇关于MS Access具有空字段的多字段搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！