Search multiple fields

[james_liv]

Morning,

Ive been converting/re-writing an old Lotus Approach database in Access. Im about 3/4's of the way through. Its been quiet a large project but I can finally see the finish line.

Some of the easiest things in Approach seem to be so difficult to achieve in Access, however, can someone assist me with the below.

I am simply trying to do a search in multiple fields. So for eg I might want to find all names 'Jones' which occurred after 24/05/20.
Or all customers in Manchester which do not have phone numbers.

Now in Approach the user would simply hit Ctrl & F and then the form fields would all be blank and you just types the criteria directly into the field and hit return (along with any operators such as '>' or '='. Any user with zero knowledge could do it.

In access I seem to have to create querys and forms etc but I am unable to predict what search the user might want to do. Surely there is a simple way of searching multiple fields for matching information?

Thanks in advance

 

[james_liv]

Thank you, it just seems insane that the most standard function of any database is the ability to search data but I need a coupon code in order to add this most basic of functions to my database.

I just assumed at first I was missing something. Im trying to set it up so users with very few computer skills can use it and left scratching my head.

 

[james_liv]

If you right click on a text-box on an MS Access Form, there are options to provide to search functionality similar to your description.

Ahh, that works quiet well. I can make use of that. Thank you.

 

[james_liv]

Access has a built in filter by form. It is simplistic but might solve your problem. For a form that you want to implement the feature, add a command button and add the following code:

DoCmd.RunCommand acCmdFilterByForm

Ahh, filter by form. Yes, that works well too. Thats great, gives me a couple of options. The only reason I am intent on this style of search is the users are not computer literate and the current system searches like that. The more I can keep this new system the same as their old one the less support they will need further down the line.

Thank you.

 

[MajP]

Take a look at this thread. You cannot build a more powerful search capability, without a ton of code. This requires some form design but very little code.

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

 

[isladogs]

@MajP
Another good candidate for the code repository methinks, Can I move that as well?

 

[arnelgp]

there is also a Class same as with MajP code.
majp uses Pop-up form, so you cannot use Toggle the Filter (Ribbon->Toggle Filter).
when I change the form to Pop-up = No, you can Toggle the filter but the "filter button" is not
being updated (still showing "filtered").