Search/Filter by multiple (optional) textboxes on form

Dear Community,

I'm relatively new to MS Access (using MS Access 2013 but the db should work on 2010, too) and try to develop a database for an NGO I'm working in. I created almost all the tables (all that I need for now) and made the relationships.

However now I start to create forms and later reports for the actual user. The database will store information about clients and track consultations and assistance the NGO gives to them. There will be around 50.000 to 70.000 clients in the main table. Every client has a specific Individual ID and is member of a family which itself has another specific Group ID.

Now here is my problem:
The User usually searches for the respective family by the Group ID. I implemented this with a search query using the ID number of a search text box. All done and no big problem.
But sometimes the ID number is not known so the user needs to search by name (First and Last Name). I use to different textboxes for this and it works in a similar way like the number search by query (Like "*" & [Forms]![frm_SearchIC]![txt_LName] & "*"). All still good However since most of the clients are actually from arabic speaking countries, converting the names into the Latin alphabet is bound to fail and produce a lot of misspellings. Therefore I added 2 more textboxes and 3 comboboxes for the user to give more information about the client and therefore make it easier to search for the person. I was able to produce a query which gives you the right result if you have ALL information at hand. However, this is not always the case.

1) But I cannot find a way to tell the query that if the a certain textbox or combobox is empty, it shall just "ignore" it and use the information at hand. I tried this in the query by adding in the criteria OR .... Is Null. This is alright for one or two textboxes but for the many I have, it seems to be too many different combinations for the criteria. It just worked with some fields but others always had to be filled in...I thought about using VBA but have no idea about it in Access (In Excel I programmed such a search-filter form).

2) If no information is given at all, the database should inform the user that he needs to enter at least on field. If nothing is found the user should get a msgBox saying "No IC matches your criteria".

3) The results of the searches should be given out in another form where the user can pick the person from 1-to-many results.

I hope I made myself some how clear. I attached a sample database with sample data and reduced tables, fields, and entries for you to better understand.

Any help and/or suggestions are highly appreciated!

Thanks in advance,
Haakon

1) But I cannot find a way to tell the query that if the a certain textbox or combobox is empty, it shall just "ignore" it and use the information at hand. I tried this in the query by adding in the criteria OR .... Is Null. This is alright for one or two textboxes but for the many I have, it seems to be too many different combinations for the criteria. It just worked with some fields but others always had to be filled in...I thought about using VBA but have no idea about it in Access (In Excel I programmed such a search-filter form).

Click to expand...

You have run into the problem of using the Design Grid, if you switch to SQL and code the Where clause there , and NEVER save the query from the design grid it becomes simple.

Where ( myfield = forms!myform!mycombo or forms!myform!mycombo is null)
And ( myfield2 =

Etc simple to do and understand.

Switch back to DG and its incomprehensible, so don't save there just save the SQL VIEW

Brian

Thanks a lot @Brianwarnock!!! I tried it out and it seems to work, unbelievable how much time I spent yesterday on this problem with no real results...

But now:
1) when the user does not enter any field of the Name Search, how can I tell him with a msgBox, that at least one field is required
2) when the database does not find any records which match the search result, instead of getting a blank form or datasheet, how can I use again a msgBox to tell the user that no results where found?

Thanks for your help!

Best,
Haakon

Unfortunately I have never done this.

I would assume that 1 is tackled in code in the command button to run the query, but 2 don't have a clue.

Your problem now is to persuade others to look at this thread. I would start a new thread, saying so on this one so that it doesn't act as a double post, focusing on the msg box issues.

Brian

You have run into the problem of using the Design Grid, if you switch to SQL and code the Where clause there , and NEVER save the query from the design grid it becomes simple.

Click to expand...

It is easy for Brian to say this .
He's very comfortable with SQL.

My approach when I run into a problem like yours is:
I design a new field in my query (in design grid) ShowThisRecord:ShowRecord() and as criteria I put TRUE.
Then, in a regular module, I design a public function:
I manage that this function to return True or False.

All you need to know is how to refer a control in a form:
Forms!TheFormName!TheControlName

Also is useful to know that the function accept parameters and you can use as parameter another field from your query.
As example, if you design the function like this one:
You can apply it from the query:
ShowThisRecord:ShowRecord(F1, F2)
where F1 and F2 are field names in your query.

This approach is not as faster as Brian's but I sustain that is a lot more flexible (and Brian will say, again, that is not true ).
I sustain this because I can change the query in design grid without the need to review my function code.
Also I can manage easier new condition if will appear in the database design process.

Good luck !

I will make no judgement on mihail method, but will say that what I said is the approach that experienced posters on here will tell you.

You do not have to code the whole query in SQL, why not make use of drag and drop and the syntax checking of the QBE GUI, I would even say that you can go as far as writing all of the query in the QBE up to adding the various

Or Forms!formname!controlname

parts of the Where clause. Of course we all know that the QBE (Design Grid) adds lots of unnecessary (), but they can be left or carefully removed.

Brian

PS It is worth looking at the SQL generated by you queries and becoming familiar with it, it is what posters on here will reply with and ask for.

Thanks again guys! I first solved it with the SQL approach Brian suggested and it worked very nice. And I have to say, it was not too easy since the code he suggested was very logic and not too complex. You just had to repeat the same kind f string with for the different fields.

However, after I got a bit more comfortable with the whole Access environment, SQL and VBA in Access, I actually switched to another solution:

With the help of VBA I first created a "Search String" based on the entries of my controls like:
"

And then in the end, I cut off the last "AND" string to have a valid SQL command.

This in return I used with the DoCmd.OpenForm Command in a filter (but I actually now think of using this in the WhereCondition instead...Is that possible???) and voila I had my search result beautiful in my new form

Additionally, the VBA-SQL approach gave me the flexibility to actually show messages if there are no results, etc. very easily...

But sure, I am open for any critique on this approach from all of you

Thanks again for your great help! And please apologize for my very very late reply..I was so busy, I didn't check the form anymore after I found a solution and somehow me email-account didn't tell me that there were more answered

See you soon for other questions,
Haakon