从查询中消除空白结果 [英] Eliminating Blank Results from Query
问题描述
我现在花了很多时间寻找这个问题的解决方案，但我尝试过的一切都没有用。我将尝试尽可能详细地帮助解释问题。
我有一个表（我们将命名为TAB1）有五列：Project＃，Contract＃，Date ，关键字，对话。 Project＃和Contract＃是数字格式，Date是日期格式，Keyword是文本格式，Conversation是备忘录格式。
我有查询（我们将调用QUER1）搜索除了Conversation之外的所有类别（因为它是备忘录，而且搜索太复杂）。
我有一个表格（我们称之为FORM1）与此查询允许用户输入他们想要搜索的内容。
我能够对查询进行编码，以便用户可以输入表单的任何或所有部分来进行搜索，以及任何空白这些部分不会被考虑在内。
因此，如果我将搜索数据输入Project＃和Contract＃并将其余部分留空，则Date和Keyword都是Null，并且表单返回任何值匹配我对Project＃和Contract＃的输入，无论条目上的日期和关键字是什么。
这一切都很完美，我没有问题。当表格中充满数据且每个单元格中都有数据时，搜索工作正常。
这里问题出在哪里：
用户输入一个新条目并将一些类别留空（即，他们输入项目＃为1，合同＃为2，但其余为空），然后查询无法正确搜索。当您进行搜索并仅输入搜索项目编号为3或其他内容时，其他类别应为空，并且只应返回3的项目编号。但由于日期和关键字类别保留为空白，因此会产生即使项目＃与搜索不匹配，每次都是正面结果。
我尝试使用NZ功能更改Null或空白条目（虽然这不是理想的解决方案，因为我不想将空白条目更改为0，我宁愿把它们留空。我试过搞乱Is Not Null搜索和几乎所有事情，我无法弄清楚。
我希望我解释得那么好，因为这是一个有点混乱的问题。如果你不明白，请询问任何澄清问题。谢谢！
I have spent numerous hours now searching for a solution to this problem, but everything I have tried just hasn''t work. I will try to be as detailed as possible to help explain the problem.
I have a table (we''ll name TAB1) with five columns: Project #, Contract #, Date, Keyword, Conversation. Project # and Contract # are number formats, Date is a date format, Keyword is text format, and Conversation is memo format.
I have a query (we''ll call QUER1)that searches through all of those categories except for the Conversation (as it is memo and is too complicated to search).
I have a form (we''ll call FORM1) tied to this query that allows the user to input what they want to search.
I was able to code the query so that the user could enter any or all parts of the form to do the search, and whatever blank sections wouldn''t be taken into account.
So if I input search data into Project # and Contract # and leave the rest blank, Date and Keyword are both Null, and the form returns any values that matched my input for Project # and Contract #, regardless of what Date and Keyword were on the entries.
This all works perfectly and I have no problems there. When the table is full of data and every cell has data in it, the search works fine.
Here''s where the problem is:
When a user inputs a new entry and leaves some of the categories blank (i.e. They enter a Project # of 1 and a Contract # of 2 but leave the rest blank), the query then doesn''t search properly. When you conduct a search and only enter to search for a Project Number of 3 or something, the other categories should be Null and it should only return Project Numbers that are 3. But since the Date and Keyword categories were left blank, it yields the entry as a positive result every time, even though the Project # doesn''t match the search.
I''ve tried using the NZ function to change the Null or blank entries (although this isn''t the ideal solution, because I don''t want to change blank entries to 0, I''d rather just leave them blank). I''ve tried messing around with "Is Not Null" searching and just about everything, and I can''t figure it out.
I hope I explained that well enough, because it is a slightly confusing problem. Please ask any clarifying questions if you don''t understand. Thanks!
推荐答案
您的帖子非常具有描述性，但缺少的是您正在使用的实际SELECT语句。这将是最有帮助的。
另外，你是如何生成搜索字符串的？通过VBA？如果您使用表单，这应该相对简单。
查询的返回结果是什么？所有匹配的记录或只是指示至少有一个匹配的记录。
但....这里的关键是你的查询本身。没有它我们就不能排除故障。
Your post was very descriptive, but the one thing missing is the actual SELECT statement you are using. This would be most helpful.
Also, how are you generating the search string? Through VBA? If you are using a form, this should be relatively simple to do.
What are the return results of the Query? All records that match or simply an indicator that there is at least one record that does match.
But.... the key here is your query itself. We can''t troubleshoot without it.
这是它的SQL代码的开头：
Here is the start of the SQL code for it:
当用户搜索项目＃为1时，它会生成表格中所有值，项目＃为1以及表格中缺少的所有值任何列中的数据（无论是Project＃列还是其他任何列）。
When the User Searches a Project # of 1, it yields all of the values in the table with a Project # of 1 as well as all of the values in the table that have missing data in any of the columns (whether it is the Project # column or any of the others).
这篇关于从查询中消除空白结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
