MS Access:如果空白则忽略查询条件 [英] MS Access: Ignoring query criteria if blank

问题描述

我在 Access 中有一个表单，我在其中运行基于多个文本框的查询.我对从文本框中拉出的几个查询字段应用条件，但希望当文本框为空时查询忽略条件.

I have a form in Access where I run a query based on several text boxes. I apply criteria on several of the query fields that is pulled from the text boxes but would like the query to ignore the criteria when the text box is blank.

例如，如果 Machine_TextBox 为空，则不对 Events.Machine 字段应用条件.

For example, if the Machine_TextBox is blank, do not apply criteria to the Events.Machine field.

SQL 代码为:

SELECT Events.Machine, Events.[Event Date], Events.[Event Description],
Events.[Action Taken], Events.[Machine Clinical], Events.[Modalities Not Clinical],
Events.[Manufacturer Ticket #], Events.[TLC Ticket #], Events.FSR, Events.ID, 
Events.[Event Recorded By], Events.[Action Recorded By], Events.[Downtime Validation],
Events.[Event Time]  

FROM Events

WHERE (((Events.Machine)=IIf([Forms]![SearchEvent]![Machine_TextBox] Is Null,"",
[Forms]![SearchEvent]![Machine_TextBox]))  AND ((Events.[Event Date]) Between 
Nz([Forms]![SearchEvent]![StartDate_TextBox],#1/1/1900#) And Nz([Forms]![SearchEvent]![EndDate_TextBox],#1/1/2100#))  
AND ((Events.[Event Description]) Like "*" & [Forms]![SearchEvent]![EventDetails_TextBox])  
AND ((Events.[Manufacturer Ticket #])=[Forms]![SearchEvent]![Manufacturer_TextBox])  
AND ((Events.[TLC Ticket #])=[Forms]![SearchEvent]![TLC_TextBox]) 
AND ((Events.FSR)=[Forms]![SearchEvent]![FSR_TextBox]))  
OR (((Events.Machine)=IIf([Forms]![SearchEvent]![Machine_TextBox] Is Null,"",[Forms]![SearchEvent]![Machine_TextBox]))  
AND ((Events.[Event Date]) Between Nz([Forms]![SearchEvent]![StartDate_TextBox],#1/1/1900#) 
AND Nz([Forms]![SearchEvent]![EndDate_TextBox],#1/1/2100#))  
AND ((Events.[Action Taken]) Like "*" & [Forms]![SearchEvent]![EventDetails_TextBox])  
AND ((Events.[Manufacturer Ticket #])=[Forms]![SearchEvent]![Manufacturer_TextBox]) 
AND ((Events.[TLC Ticket #])=[Forms]![SearchEvent]![TLC_TextBox])  
AND ((Events.FSR)=[Forms]![SearchEvent]![FSR_TextBox]))

ORDER BY Events.[Date and Time Stamp] DESC;

真诚的，

标记

推荐答案

您可以尝试此处描述的技术.

You can try the technique described here.

对于每个搜索框，使用布尔逻辑来过滤其值，或者通过使 AND 子句为 TRUE 来忽略此 AND 子句(如果它为空).

For each search box, use boolean logic to either filter for its value, or ignore this AND clause if it's empty, by making the AND clause TRUE.

我将仅使用两个搜索框作为示例:

I'll just use two search boxes as example:

SELECT stuff
FROM Events

WHERE ((Events.Machine = [Forms]![SearchEvent]![Machine_TextBox]) 
            OR ([Forms]![SearchEvent]![Machine_TextBox] Is Null))
  AND ((Events.[Event Description] Like "*" & [Forms]![SearchEvent]![EventDetails_TextBox] & "*") 
            OR ([Forms]![SearchEvent]![EventDetails_TextBox] Is Null))
  AND ...

这篇关于MS Access:如果空白则忽略查询条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！