添加NOT LIKE条件后，查询返回的结果太少 [英] Query returning too few results after adding NOT LIKE condition

问题描述

我有一个Access 2010数据库，当我稍稍更改了用户请求的查询时，该数据库的行为就很奇怪.我有一个很大的表(不要问)，查询将在该表上运行，然后提供给用户输入表单.无论如何，今天，一种特殊的表格开始返回的结果比我们预期的要少得多.它通常返回大约1200个结果，但是在进行较小的更改后，该结果下降到880个结果.当我检查它时，它应该只下降到1100，因此我错过了另外300个预期结果.

I have an Access 2010 Database that is behaving oddly when I slightly altered a query that a user requested. I have one very large table (don't ask) that the query runs on and then feeds to a user input form. Anyway, today, one particular form started returning a lot less results than we expected. It normally returns about 1200 results, but after the minor change, that dropped to 880 results. When I was checking it, it should only have dropped to 1100, so I am missing an additional 300 expected results.

我对该表进行分层查询，以防止事情变得太奇怪.我对sql不太满意，因此通常只使用设计视图进行构建.如果必须的话，我可以在sql中摸索一下.

I build my queries to this table in layers to keep things from getting too strange. I'm not great with sql, so I usually build just using the design view. I can muddle through in sql if I have to.

这是第一层的SQL

SELECT [New ZSPWAR Prime].Payer, [New ZSPWAR Prime].Bill_doc, [New ZSPWAR Prime].Sqwak, [New ZSPWAR Prime].Billing_Dt, [New ZSPWAR Prime].Issued_Material, [New ZSPWAR Prime].Serial_No, [New ZSPWAR Prime].[review date], [New ZSPWAR Prime].Mtrl_Qty_Billed, [New ZSPWAR Prime].PO_DT, [New ZSPWAR Prime].PO_NO, [New ZSPWAR Prime].PO_PI, [New ZSPWAR Prime].INV_PT_DT, [New ZSPWAR Prime].[Warr Typ], [New ZSPWAR Prime].Stage, [New ZSPWAR Prime].Returned_Material, [New ZSPWAR Prime].Returned_SNR, [New ZSPWAR Prime].Ret_EQUI, [New ZSPWAR Prime].Sales_Ord, [New ZSPWAR Prime].SVO_No, [New ZSPWAR Prime].[ME23N PO_Owner], [New ZSPWAR Prime].Ret_Note, [New ZSPWAR Prime].Ret_Date, [New ZSPWAR Prime].Ret_Note_DESC, [New ZSPWAR Prime].WACD, [New ZSPWAR Prime].Wty_Prog_Code_Desc, [New ZSPWAR Prime].VR_Amount, [New ZSPWAR Prime].Freight_Amt, [New ZSPWAR Prime].VDCD, [New ZSPWAR Prime].Ven_Den_Code_Desc, [New ZSPWAR Prime].ZZ07, [New ZSPWAR Prime].[root cause] AS [Email Follow Up], [New ZSPWAR Prime].[Step 1 Complete], [New ZSPWAR Prime].[Step 1 notes], [New ZSPWAR Prime].[Step 2 complete], [New ZSPWAR Prime].[step 2 notes], [New ZSPWAR Prime].[Step 3 complete], [New ZSPWAR Prime].[step 3 notes], [New ZSPWAR Prime].[Push INV_PMT], [New ZSPWAR Prime].[PO Due Date]
FROM [New ZSPWAR Prime]
WHERE ((([New ZSPWAR Prime].Payer) Not Like "10208" And ([New ZSPWAR Prime].Payer) Not Like "2300" And ([New ZSPWAR Prime].Payer) Not Like "32770") AND (([New ZSPWAR Prime].Billing_Dt)>#7/1/2016#) AND (([New ZSPWAR Prime].Mtrl_Qty_Billed)>0) AND (([New ZSPWAR Prime].PO_NO) Not Like "" And ([New ZSPWAR Prime].PO_NO) Is Not Null) AND (([New ZSPWAR Prime].[Step 3 complete])<>Yes));

这是第二个:

SELECT [Day2Day InvPmt a].Payer, [Day2Day InvPmt a].Bill_doc, [Day2Day InvPmt a].Sqwak, [Day2Day InvPmt a].Billing_Dt, [Day2Day InvPmt a].Issued_Material, [Day2Day InvPmt a].Serial_No, [Day2Day InvPmt a].[review date], [Day2Day InvPmt a].Mtrl_Qty_Billed, [Day2Day InvPmt a].PO_DT, [Day2Day InvPmt a].PO_NO, [Day2Day InvPmt a].PO_PI, [Day2Day InvPmt a].INV_PT_DT, [Day2Day InvPmt a].[Warr Typ], [Day2Day InvPmt a].Stage, [Day2Day InvPmt a].Returned_Material, [Day2Day InvPmt a].Returned_SNR, [Day2Day InvPmt a].Ret_EQUI, [Day2Day InvPmt a].Sales_Ord, [Day2Day InvPmt a].SVO_No, [Day2Day InvPmt a].[ME23N PO_Owner], [Day2Day InvPmt a].Ret_Note, [Day2Day InvPmt a].Ret_Date, [Day2Day InvPmt a].Ret_Note_DESC, [Day2Day InvPmt a].WACD, [Day2Day InvPmt a].Wty_Prog_Code_Desc, [Day2Day InvPmt a].VR_Amount, [Day2Day InvPmt a].Freight_Amt, [Day2Day InvPmt a].VDCD, [Day2Day InvPmt a].Ven_Den_Code_Desc, [Day2Day InvPmt a].ZZ07, [Day2Day InvPmt a].[Email Follow Up], [Day2Day InvPmt a].[Step 1 Complete], [Day2Day InvPmt a].[Step 1 notes], [Day2Day InvPmt a].[Step 2 complete], [Day2Day InvPmt a].[step 2 notes], [Day2Day InvPmt a].[Step 3 complete], [Day2Day InvPmt a].[step 3 notes], [Day2Day InvPmt a].[Push INV_PMT], [Day2Day InvPmt a].[PO Due Date]
FROM [Day2Day InvPmt a]
WHERE ((([Day2Day InvPmt a].INV_PT_DT) Is Null) AND (([Day2Day InvPmt a].[Step 1 notes]) Not Like "*" & "windsh*")) OR ((([Day2Day InvPmt a].INV_PT_DT) Like "") AND (([Day2Day InvPmt a].[Step 1 notes]) Not Like "*" & "windsh*"))
ORDER BY [Day2Day InvPmt a].Bill_doc;

罪魁祸首在这里:

WHERE ((([Day2Day InvPmt a].INV_PT_DT) Is Null) AND (([Day2Day InvPmt a].[Step 1 notes]) Not Like "*" & "windsh*")) OR ((([Day2Day InvPmt a].INV_PT_DT) Like "") AND (([Day2Day InvPmt a].[Step 1 notes]) Not Like "*" & "windsh*"))

不包括"windsh *"的部分是最近添加的内容和引起混乱的原因.

The part excluding "windsh*" is the recent addition and source of confusion.

在添加一点点之前，我得到了1200个回报.之后，我得到880.我尝试反向构建它，只有100个或应该被过滤掉，我不知道为什么要这样做.有任何想法，还是在这里的其他地方得到了答案?

Prior to adding that little bit, I was getting 1200 returns. After, I get 880. I tried to build this in reverse and There are only 100 or that should have been filtered out, and I have no idea why it's doing this. Any ideas, or has this been answered elsewhere in here?

预先感谢

推荐答案

Not Like运算符与Null值不匹配，即使Null值显然与模式不匹配.您需要显式(重新)包含Null.

The Not Like operator does not match Null values even though a Null value apparently does not match the pattern. You need to explicitly (re)include Nulls.

在条件网格的设计视图"中，输入类似的内容

Within Design View on the criteria grid you enter something like

Not Like "*windsh*" Or Is Null

在SQL语句中应该类似于

Within the SQL statement is should look something like

(([Day2Day InvPmt a].[Step 1 notes]) Not Like "*windsh*" Or ([Day2Day InvPmt a].[Step 1 notes]) Is Null)

这篇关于添加NOT LIKE条件后，查询返回的结果太少的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！