Problem with dates in multi criteria search form if a record doesn't contain a date

[Roonmeister99]

Hi Guys,



Having issues with a date search in a multi criteria search form. My database field is called DateComplete
I want the query to return records that fall between a certain criteria IF the user has specified a date range, but
ignore the date if no range has been specified. The problem is that DateComplete is not always set


This works if there are dates specified- if DateComplete >=[Forms]![NewSearchForm]![CompleteFrom] And <=[Forms]![NewSearchForm]![CompleteTo]
i've tried adding OR ISNULL - but this brings back all records, and ignores any other criteria set on the form!


I hit a problem where access would throw an error if no dates were input so I had to set the CompleteFrom to the date the database was
created and the CompleteTo to todays date - unsure if this is compounding the issue?

Many thanks

[Minty]

You nearly have the solution. You need to specify the criteria you are checking in the is null expression.

>= [Forms]![NewSearchForm]![CompleteFrom] OR [Forms]![NewSearchForm]![CompleteFrom] Is Null

[Roonmeister99]

That worked! Thank you so much