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