Unanswered: Query won't return records with empty value in field Version: 2003 (11.0)
I have created a form [frmFind] to populate another form based upon a query. In the query, I want to find the records with matched data in several fields. If nothing is in the field, then that will not be part of the criteria. All goes well except when I reach the date field [EffectiveDate]. If I don't want to filter by date, I put nothing in the [EffectDateSearch] field. However, when I execute the query, it will only return the records where [EffectiveDate] is not null, but i want to see them all.
IIf([Forms]![frmFind]![EffectDateSearch] Is Null,[EffectiveDate],[Forms]![frmFind]![EffectDateSearch])
I have used this logic on other fields in the query and it works fine. Any suggestions? Thanks, i rarely use Access. I am sorry to be a bother.