我在Access中有一个表单,我在其中运行基于几个文本框的查询.我对从文本框中提取的几个查询字段应用条件,但希望查询在文本框为空时忽略条件.
例如,如果Machine_TextBox为空,则不要将条件应用于Events.Machine字段.
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;
此致,
解决方法
您可以尝试
here所描述的技术.
对于每个搜索框,使用布尔逻辑来过滤其值,或者通过使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 ...
