I have a table in access (simplified) with fields of sex, first name, last name, and phone number.
Sex First Last Phone
F Alice Smith
M Bob 111-111-1111
F Smithe 111-111-1112
M Charlie Smith
F Eve Drop 111-111-1113
I have created a form along with a query to search for the records, using the criteria of
Is Null Or Like "*" & [Forms]![Search]![Criteria] & "*"
where Search is the name of my form, and Criteria is the name of the individual entries on my form.
Right now, if I search for Sex F and the last name Smith, and leave the first and last name fields blank, I want to be able to see the records for "Alice Smith" and "Smithe". But instead, I only get the record of "Alice Smith". Furthermore, if I search for Sex F and phone number 111, I get results for Alice, Smithe, and Eve. Similarly, if I just search for phone number alone, I will get all five records.
I'm assuming that I'm doing something wrong here, but I can't tell what it is. Based on the conditional logic of or, the results should be either Null or 111 (and similar for the other fields), so I guess it is behaving as expected. I've tried xor however, and I get the same result (and even if it does work the way I'm thinking it would, it would seem bad since a blank entry would be interpreted as null instead of "search every entry". I've tried using iif, which should be the correct way to do this, but it appears to not work when placed in the criteria field. Am I missing something here?
