Query criteria with date values

Question

I am trying to build a query with criteria in two different fields.

One will search through the company name, for which I use

Like "*" & [Forms]![Date_Search]![CMBLdays_Start_DS] & "*"

If there is a corresponding value it is returned
If the textbox value in my form equals null or "" then it brings me results based on values of my other textboxes.

I would like to do this with two date values as well.

If there is a date range, let's say 01/01/19 to 25/03/2019 then I would like to filter that field based on the range values provided in the textboxes.
In the event the textboxes should be empty I would like to ignore that criteria and bring results based on my textbox value for company name.

At the moment I am using

>=[Forms]![Date_Search]![CMBLdays_Start_DS] and <=[Forms]![Date_Search]![CMBLdays_End_DS]

It returns the window of dates as requested if there is a value in both textboxes.
It will not return any if the textboxes are left blank, which is an issue.

Lee Mac Lee Mac · Accepted Answer · 2020-11-23T18:37:09

You can include an is null alternative condition within your selection criteria, e.g.:

select * from YourTable where
(
    [Forms]![Date_Search]![CMBLdays_Start_DS] is null or 
    YourDate >=[Forms]![Date_Search]![CMBLdays_Start_DS]
) 
and 
(
    [Forms]![Date_Search]![CMBLdays_End_DS] is null or 
    YourDate <=[Forms]![Date_Search]![CMBLdays_End_DS]
)

That is to say, either the form value is blank (null) or your date field is within the date range defined by the form value.

Here, YourDate represents the date field to which your selection criteria is being applied.

Query criteria with date values

2 Answers