1

Given a Query with the criteria that uses text boxes in access. It works fine.

Like IIf(IsNull([forms]![f_form]![txt_box]),"*",[forms]![f_form]![txt_box])

The above criteria works fine when a cell has a value. However, a lot of cells in the table which I search in, is null.

My best and logic shot was:

   Like IIf(IsNull([forms]![f_form]![txt_box]),"*",[forms]![f_form]![txt_box])
OR Like IIf(IsNull([forms]![f_form]![txt_box]),Is Null,[forms]![f_form]![txt_box])

However, this does not work.

If i just use "Or Is Null" then it returns all records where a cell is null even when i do a specific search.

Any suggestions?

BR, Emil.

Edit: The solution so far was to put in "-" in all null cells, but when the users leave a cell blank (Null), the record will never show up in the filter (Query). Hence I do not prefer this solution.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Emil Olsen
  • 332
  • 1
  • 9
  • 25
  • Possible duplicate of [How to filter by what the user enters in a textbox, except show all if it's empty](http://stackoverflow.com/questions/38850333/how-to-filter-by-what-the-user-enters-in-a-textbox-except-show-all-if-its-empt) – Andre Aug 10 '16 at 08:59
  • Hi @Andre , It is not a dublicate, i have looked into that question. The issue is when the record is Null, not when the textbox is null. Do you have a suggestion? – Emil Olsen Aug 10 '16 at 09:11
  • BTW, When i use the suggestion you just sent, the search will be with the stars * . The users want a search of 330 to only find 330, not 3300, 3301 etc. That will happend if I do the * & textbox & * you refer to. – Emil Olsen Aug 10 '16 at 09:14
  • The asterisks * are a detail that can be left out in your query. But don't you want the same thing? 1. If the search text box is empty, return all records. 2. If not, do a search? Then you can use the same concept. – Andre Aug 10 '16 at 09:18

2 Answers2

1

What I mean is this criteria:

[Forms]![f_form]![txt_box] OR ([Forms]![f_form]![txt_box] Is Null)

or if you want to support wildcard searches too (after all):

LIKE [Forms]![f_form]![txt_box] OR ([Forms]![f_form]![txt_box] Is Null)
Andre
  • 26,751
  • 7
  • 36
  • 80
  • That does not work inside the IIF as far as i can see! – Emil Olsen Aug 10 '16 at 10:33
  • You don't need `IIf`, and no `Like` either. This filters by the search textbox (if not empty) or returns all records. – Andre Aug 10 '16 at 10:36
  • Yes, you are right. That is working. Thank you very much! – Emil Olsen Aug 10 '16 at 10:56
  • Nah, i was happy too quick. When i search 33* for example, it just returns #Name? in ALL cells.. Why is that? – Emil Olsen Aug 10 '16 at 11:00
  • See edit... I thought you wanted only exact searches. – Andre Aug 10 '16 at 11:08
  • Its closer, but, when i search for something that does not exist anywhere in the table, it returns the lines with Null cells. It should not... – Emil Olsen Aug 10 '16 at 12:32
  • For what for example? This shouldn't happen - either you have the criteria wrong (please show the exact criteria you have now), or there is something else going on you haven't told us about. – Andre Aug 10 '16 at 12:51
  • As I see your criteria, it lets through *keyword* OR a cell that is Null (Hence the OR Is Null criteria). So if i search for "mkdlasmdklsa" which is not present, it returns nothing BUT the null cells. The criteria i use is the one exact as you typed it. – Emil Olsen Aug 10 '16 at 12:55
  • That's not possible. The criteria would resolve to: `LIKE "mkdlasmdklsa" OR ("mkdlasmdklsa" Is Null)`. The part after the OR is always FALSE if the search textbox is not empty. This is very different from `OR Is Null`. – Andre Aug 10 '16 at 13:01
  • The query is very big. I will try and implement the criteria in all levels and get back to you. Should i update the question with the solutions tried and images or just post here? – Emil Olsen Aug 10 '16 at 13:15
  • Anything larger than a tiny code snippet isn't suited well for comments, and should better be edited into the question. – Andre Aug 10 '16 at 13:18
  • @EmilOlsen: For multiple search fields, see also here: http://stackoverflow.com/a/38899574/3820271 – Andre Aug 11 '16 at 15:20
1

I would suggest using NZ instead of IIF and IsNull combination,

SearchField Like Nz([forms]![f_form]![txt_box],'*')

Do you think that the txt_Box can have Zero length string instead of Null or possible that either of one can be there ? if so you can try following

SearchField like iif([forms]![f_form]![txt_box]='','',nz([forms]![f_form]![txt_box],'')

Sham Yemul
  • 463
  • 7
  • 30