MS Access: Ignoring query criteria if blank

Question

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

Welcome. Please format your code so that it is more readable. The lines are hundreds of characters long at the moment — Mark Chorley
Typically in this situation I'll use variables to catch the field values, then check if they're null or empty. If not, I'll append the table field name to the value and any formatting for the specific data type, then add that variable to the query. This way if it's empty, when the variable is added to the query, essentially it is ignored. — Michael Russo
@MichaelRusso, thank you for your response. I was hoping to be able to avoid re-structuring the way I have built the query. To confirm, is there no way I could use an "IIF-IsNull" expression and pass "Is Not Null" should the IIF statement return as true (i.e. the text box is Null)? — Mark Bray-Parry
I don't think the IIF will work in this case as it stands. If you look at the first WHERE condition, when the text box is null, you're asking for records where Events.Machine = "" which I'm pretty sure you don't want — Michael Russo

Andre Andre · Accepted Answer · 2016-08-11T14:44:57

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: Ignoring query criteria if blank

1 Answers