databasems-accessiif

Access Search Query Criteria : IF false return all records

I am trying to use a form to search in a database. One of those forms contains a date.

If The user types in a date, it will only return results from that specific date.

But, if no date is entered, I want to show ALL records, including the records without a date.

This is the furthest I've gotten so far;

IIf(IsNull([Forms]![OrderSearchMenu]![DisplaySearchDate]),Like "*" Or Is Null,[Forms]![OrderSearchMenu]![DisplaySearchDate])

If I remove the "Or is Null" then it works fine, but will not show the records without a date. With it, it just does not work at all.

So, clearly you can't use an "or" statement in an "iif" statement like this... So how?

Any help to nudge me in the right direction will be very much appreciated!

Solution

  • Consider:

    Nz(DepartureDate, 0) LIKE IIf(Forms!OrderSearchMenu!DisplaySearchDate Is Null, "*", Forms!OrderSearchMenu!DisplaySearchDate)

    For more info review Microsoft Access Query Should return true or true and false, only returns true and when unknown in combobox is selected show all null values