How to filter by what the user enters in a textbox, except show all if it's empty

Question

I want to filter a form with a query, so that the user has to input what he would like to search in a textbox and it gets filtered out. There are some empty fields, and these should show if the user has not searched for anything yet, but if he searches those should not be shown anymore.

With this code, the searching works, but it shows the empty fields:

Like("*" & [Forms].[BerichtSuche].[efTitle] & "*") OR Is Null

I tried this, but then nothing at all is shown:

If(Len([Forms].[BerichtSuche].[efTitle].[Text])=0; 
  Like("*" & [Forms].[BerichtSuche].[efTitle] & "*") OR Is Null; 
  Like("*" & [Forms].[BerichtSuche].[efTitle] & "*"))

question unclear: filtering or sorting ? It's not the same ! — iDevlop, Aug 09 '16 at 12:14
Just remove the `is null` part from your code. When the form opens with no filter applied, the nulls will display. Once they start typing the filter becomes active and the nulls are filtered out normally. — random_answer_guy, Aug 09 '16 at 12:30
@random_answer_guy I thought so too but it filters all of the empty fields out — TheSevinator, Aug 09 '16 at 12:32
Show input data, desired results, actions taken to get there, and the actual results you're seeing. — underscore_d, Aug 09 '16 at 12:36
@underscore_d Can't it's from my workplace and I'm not allowed to show any data — TheSevinator, Aug 09 '16 at 12:39

score 0 · Accepted Answer · answered Aug 09 '16 at 13:27

0

So basically, if the search field is empty (Null), you want to show all records.

Use Boolean logic to make the criterion always True in this case:

(Like "*" & [Forms].[BerichtSuche].[efTitle] & "*") 
OR ([Forms].[BerichtSuche].[efTitle] Is Null)

answered Aug 09 '16 at 13:27

Andre

26,751
7
36
80

score -1 · Answer 2 · answered Aug 09 '16 at 13:30

-1

if nz(textbox.value,"") <> "" then 
    do the filter
    set the filter on. 
    or generate your own sql "Select fields from table where (field like '"& textbox.value &"') -> use the sql as rowsource/recordsource
else 
   don't do or
   clear the filter 
end if

answered Aug 09 '16 at 13:30

Krish

5,917
2
14
35

I wasn't aware pseudocode qualified as an answer when the OP is asking about a specific language. – underscore_d Aug 09 '16 at 13:44
You are not aware of few things. Especially, this site serves everyone and some might be interested in different approaches. besides, i'd prefer showing them how to do than do their homework! @underscore_d – Krish Aug 09 '16 at 14:02

How to filter by what the user enters in a textbox, except show all if it's empty

2 Answers2

Linked