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

问题描述

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

例如，如果Machine_TextBox为空白，则不要将条件应用于Events.Machine字段.

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;

您真诚的

标记

解决方案

您可以尝试在此处中描述的技术. /p>

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

我将仅以两个搜索框为例:

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 ...

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.

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

SQL code is:

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;

Yours sincerely,

Mark

解决方案

You can try the technique described here.

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屋！