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

 

[Uncle Gizmo]

See Allen Brownes search form

Microsoft Access tips: Search criteria

Free sample database for Microsoft Access 2000 and later, demonstrating how to build criteria from many optional entries, and handle different field types, exact matches, partial matches, and ranges.

allenbrowne.com

 

[Uncle Gizmo]

I also have my own Search Form which isn't as comprehensive as Allen's.

Nifty Search Form - Nifty Access

Nifty Search Form This Advanced but simple to use Search Form is unbelievably easy to set-up. Just create a query and a subform. Following the comprehensive Video Instructions, which demonstrate how to add this incredible utility to your database. Establish yourself as the "Go To" person in your...

www.niftyaccess.com

Contact me if you want a free copy and I will send you a Coupon Code. .

 

[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.

 

[Uncle Gizmo]

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

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.

 

[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.

 

[Pat Hartman]

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

 

[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.

 

[Pat Hartman]

You can't have it both ways. If they are not sufficiently computer literate to be able to use the filter by form then they cannot have a free-for-all search mechanism. I've created many complex search forms for users. They are not difficult but they are limited. You can't all of a sudden add field xyz on the fly. The search forms should provide the major selection criteria. If the client needs other, more detailed options that you are not able to incorporate in the standard search, then allow them to do exports to Excel and search there. Frequently, I return data in datasheets. The ds has many search/sort options that are very similar to Excel and the users can work with that also. When making a form specifically that returns the results of the primary search and displays the results as a ds that allows further filtering using Excel methods, just make sure that the form is not updateable to prevent accidents.

 

[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").