query to filter on specific data or no filter if blank

Question

I have a query which filters records based on dates (start date and end date)selected in a previous form. I want the query to filter the specific date range, or output all records if the fields are left blank.

I am unfamiliar with SQL. is there a way to add an if-then statement?

I can use vba if necessary, but would like to use the Access GUI if it is possible.

if you just need to view your records, you can use VBA code for filters. It is alot easier to understand how to use instead of using SQL. You can check out https://msdn.microsoft.com/en-us/library/office/ff194672.aspx on how to use the filter property — SunRay, Mar 28 '16 at 13:44

score 0 · Answer 1 · answered Mar 28 '16 at 13:27

You can use these criteria for StartDate and EndDate respectively to compare them to themselves in case one (or both) of the search fields on the form is empty (Null):

>=Nz([Forms]![YourForm]![FromDate], [StartDate])

<=Nz([Forms]![YourForm]![ToDate], [EndDate])

score 0 · Accepted Answer · answered Mar 28 '16 at 13:29

If you have a parameter, used in WHERE clause (Criteria in query builder) and you want to show all records if parameter is empty, just add this parameter as new column and OR condition where indicate Is Null or, better add a column with expression Nz([MyParam],"") and in Condition area inORrow add""`. Unfortunately in query builder this construction may be quite complicated if you have few parameters, in SQL it looks much simpler, for instance in your case it will be something like this:

WHERE (MyDate >= [paramDateStart] and MyDate <= [paramDateEnd]) 
      OR (Nz([paramDateStart],"")="" AND Nz([paramDateEnd],"") = "")

Sometimes simpler edit SQL and then switch to Design view

score 0 · Answer 3 · edited Mar 22 '23 at 09:17

In MS-Access, use NZ to return TRUE when your [FilterField] in a query test if [FilterField] matches [FieldNameToCheck] that you want to filter to when [FilterField] is not null. NZ can set the result for all columns to TRUE of [FilterField] is null, so setting the CRITERIA to TRUE works just the way you want it to. i.e. Value will be TRUE on all lines if [FilterField] is null, or only true on the selected row matching a non-null [FilterField] selection:

Column 'Show' can be deselected in the query so that you don't see the column.

In 'Field' type this:

TEST: NZ([forms]![myformname]![FilterField]=[FieldNameToCheck],True)

In 'Criteria' type the word TRUE

Hope this helps?

query to filter on specific data or no filter if blank

3 Answers3