Multi Word Search (1 Viewer)

[Weekleyba]

I'm looking for some guidance on how to create a multiple word search, similar to the way Google works.
I did find this DB by John Big Booty that is fantastic but only will search one column at a time. (or at least that's the way it appears)

https://www.access-programmers.co.uk/forums/showthread.php?t=188663

What I would like the search to do is, to be able to search for multiple words across multiple fields from the query associated with the List Box.

So for example, typing in the Search For text box "Shiraz SA" would return all the records the have Shiraz for the type of Grape and SA for the State.

Can John's DB be modified to create such a search?

Thanks,
Brad

 

[The_Doc_Man]

Damned near ANYTHING can be done with VBA if it is possible at all, but that doesn't meant it would be easy.

The first thing that comes to mind is that if you type something arbitrary, say "ABC DEF" then the question would be whether you would find "ABC DEF" in one of the fields you are searching OR whether you would ALWAYS want to find "ABC" in one field and "DEF" in another. And another question is whether that Search is an AND or an OR search. For example, would you want to see any records with "ABC" in the first field REGARDLESS of what is in the second field (and similarly, "DEF" in the second field regardless of what is in the first field)? Or is this going to require "ABC" in the first field and at the same time, "DEF" in the second field of the same record?

Also, if you typed in "ABCDEF" do you want to only search one field? Or would you want to search every searchable field for the same thing? I.e. find "ABCDEF" in field one as well as finding "ABCDEF" in field two?

Clarify your intent before starting down this road.

 

[jdraw]

see this sample http://www.accessforums.net/showthread.php?t=43055&p=281588#post281588

 

[Weekleyba]

Thanks Doc Man for the response.

Let me try and clarify what I 'think' I'd like to see happen with the search.

Following your same example,
if one types in "ABC" then all fields are searched and displayed. I believe that is how it is now in John's database.
if one types in "ABC DEF" then I would want to always find "ABC" in one field and "DEF" in another, for the same record. John's database is not able to do this.
Similarly, I think it would be best to be an AND search so both "ABC" and "DEF" have to be in the same record (different fields) in order to be displayed in the search results.

Since I haven't the foggiest how to make this happen, I hope my requirements haven't made it impossible.

Thanks for your help,
Brad

 

[Weekleyba]

Thanks Jdraw, you've given me something to chew on.
I'll take a deeper look at it tomorrow.
That's a lot of code to figure out. I'm very much a beginner with VBA but learning....

 

[jdraw]

import the materials into your database, delete the tables from the sample.

It isn't meant for volumes and volumes.

See this for another poster's situation.

 

[Uncle Gizmo]

I've recently started a VBA course which Leads the student(s) through the steps required to build this search form:-

Advanced Search 0 - Nifty Access



If that looks like a possible solution for you, have a look at this webpage:-

Build an Advanced Search Form

where I provide links to the my various YouTube playlists which show you how to develop the code right from the beginning. If you're interested then send me an email and I will sign you up to the Free Advanced MS Access Course!

 

[bastanu]

hi Weekleyba,

Here is a sample database with a custom filter pop up form and another form used to run queries. You can apply the filter with AND/OR (to apply it to the same field to look for multiple values use OR an change the expression number field to be the same for all "or" clauses).

Cheers,
Vlad

 

[arnelgp]

here is another sample
multiple field search within
a record.

on the Click Event of the Search
button, change the name of the
table (NewCustomer) to your table name.

also make sure your table has autoNumber
field.

still on the code of Search button,
change ID to the name of your AutoNumber
field.

 

[Galaxiom]

So for example, typing in the Search For text box "Shiraz SA" would return all the records the have Shiraz for the type of Grape and SA for the State.

Multifield searches rarely make sense. For example, what is the point of searching the State field for "Shiraz" and the Grape field for "SA"?

 

[arnelgp]

i think to the OP it makes
more sense. it makes sense to
me. think of it as googling the
web with multiple search criteria.

 

[Galaxiom]

If you are searching all fields and wanting the results to tell you which field they are in then you should probably be using an entity-attribute-value structure.

EntityID | Attribute  | AttributeValue

 1       | Name       | Dorrien Estate 35th Anniversary
 1       | Grape      | Shiraz
 1       | Vintage    | 2016
 1       | Region     | Barossa
 1       | ListPrice  | $330
 1       | Rating     | 1
 1       | Alcohol    | 14.5%
 1       | Colour     | Intense bright crimson hue, displaying great depth of colour.
 1       | Palate     | Blueberry, dark cherry 
 1       | Character  | Cedar, Sandalwood
 1       | Tannin     | Soft
 1       | Nose       | A bright and lifted array of red and blue fruits ranging from red cherry to blueberry aromas, complexed by underlying hints of black pepper and spice.

This way you search one field only and return the Attribute and Entity. Or you can search for the word in particular attributes. All this using mostly the same query with parameters for the search term and attribute. It is also easy to return the complete list of attributes for an entity.

This structure allows more attributes to be added without changing the design.

The form structure is a subform for the attribute/value pairs.

 

[Galaxiom]

i think to the OP it makes
more sense. it makes sense to
me. think of it as googling the
web with multiple search criteria.

To the user, Google is like searching one field containing the whole text of the page, and returning the pageID.

Google actually works by looking up an index of words to return the pageIDs that contain those words. That is not a search of multiple fields either.

 

[arnelgp]

here is the revision of
my previous search form.

i added conditional formatting
so you will know which field(s)
has hits on the search.

Galaxiom:

how about those sites that displays
additional filters for search, like
if i search a movie, and i like to refine
my search, they will present me with:

[Top 10]
[Latest]
[Genre]
	[Action]
	[Thriller]
	[Comedy]
	[Drama]
[By Year]
	[2018]
	[2017]
	[2016]
	[Older]

is this a single field also, ow..

 

[Weekleyba]

Hey Gizmo, I'm going to take you up on your VBA for beginners videos.
I'm finding myself always running into situations that demand VBA.
I guess that's what happens when you start wanting to do more and more complex things with your database.
I sent you an email.
Thanks for posting that.

 

[Weekleyba]

Thanks Arnelgp! You sample database is right along the lines of something I can use.
Now I need to dissect it and learn how it works.
I sure appreciate you sharing it.

 

[arnelgp]

I think you can manage it. You only need to replace the tablename and the fieldname of tbe form you will use it on