How to search multiple words in a single search box (1 Viewer)

tharukan

New member
Local time
Tomorrow, 03:14
Joined
Aug 12, 2020
Messages
7
Hi All. First of all, I'm new to this forum and not much fluent in MS Access.

This is my issue. I have created an Access form to search for data that I have entered in the Access table. I have done it using a Macro builder. It works for a single word search. But I want to search using multiple keywords. It can search using multiple values in a single text box. But,

My issue is, it searches for all the words in the Access table that matches the multiple inputs. But I want to search and get records that contain the multiple search values I have searched for.
Eg:- Table contains These Names

Sanath Anton Perera
Sanath Kumara
Julian Sanath Perera

I want to search for 'Sanath Perera'.
Then I only want the results containing all the values I searched.

The search results that I want is,
'Sanath Anton Perera'
'Julian Sanath Perera'

Hope you got the question. And I would like to post the code that I used. I would like to thank @jdraw and @Brianwarnock for their amazing work.
Code is attached.
 

Attachments

  • code.txt
    2.5 KB · Views: 226
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 17:44
Joined
Jan 23, 2006
Messages
15,364
It would be better if you would attach a copy of your database.

If the name to be searched is all 1 string, you could try

Code:
Select * from yourtable
Where yourNameField like "*Sanath*Perera"

Welcome to AWF.
 

tharukan

New member
Local time
Tomorrow, 03:14
Joined
Aug 12, 2020
Messages
7
Thank you for your reply.
The database is a bit heavy. I'll try to attach it.
There are many names. Not only this name to be searched.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 21:44
Joined
Sep 21, 2011
Messages
14,047
As jdraw has indicated, replace your spaces with an asterisk and use that string.?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:44
Joined
Feb 28, 2001
Messages
27,001
In general, when you put text in a box and search for it, you will only find exact matches. If you put "Sanath Perera" in, you would only find your "Julian Sanath Perera" using a "LIKE" search with wildcards appended on either end of your text box. However, your description says you want a record that contains both words separately. There is no SQL operator that will easily do this for you. But a little bit of pre-processing might help here. Before you do the search, test whether there is a space between words. Let's say the value to be searched is in your form control called txtSearch. You would not search for that box's contents directly. You used a SPLIT but there is another way that doesn't require a loop for the parts of the search string.


stSRH = "SELECT * FROM Main WHERE fieldname LIKE * " & REPLACE( TRIM( txtSearch ), " ", "*" ) & "* ;"

You are still searching every field, so there is more to it than this. But the idea is that your txtSearch content would make "Sanath Perera" become "Sanath*Perera" as a result of that trim/replace operation. So that would catch more cases in a single pass.

I"ll think about this more later, but I could easily reduce that loop to a single SQL statement that you could then open as a recordset to find all matches (and nothing else) in a single query or a limited set of queries.
 

tharukan

New member
Local time
Tomorrow, 03:14
Joined
Aug 12, 2020
Messages
7
In general, when you put text in a box and search for it, you will only find exact matches. If you put "Sanath Perera" in, you would only find your "Julian Sanath Perera" using a "LIKE" search with wildcards appended on either end of your text box. However, your description says you want a record that contains both words separately. There is no SQL operator that will easily do this for you. But a little bit of pre-processing might help here. Before you do the search, test whether there is a space between words. Let's say the value to be searched is in your form control called txtSearch. You would not search for that box's contents directly. You used a SPLIT but there is another way that doesn't require a loop for the parts of the search string.


stSRH = "SELECT * FROM Main WHERE fieldname LIKE * " & REPLACE( TRIM( txtSearch ), " ", "*" ) & "* ;"

You are still searching every field, so there is more to it than this. But the idea is that your txtSearch content would make "Sanath Perera" become "Sanath*Perera" as a result of that trim/replace operation. So that would catch more cases in a single pass.

I"ll think about this more later, but I could easily reduce that loop to a single SQL statement that you could then open as a recordset to find all matches (and nothing else) in a single query or a limited set of queries.
Thank you for your kind reply. I put your code and it works perfectly.
 

tharukan

New member
Local time
Tomorrow, 03:14
Joined
Aug 12, 2020
Messages
7
The database is attached.
 

Attachments

  • test.accdb
    1.7 MB · Views: 340

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:44
Joined
Feb 28, 2001
Messages
27,001
If you are trying to make a truly general search method, you might do something like this.

Code:
PRIVATE SUB CMDSEARCH_CLICK()

DIM stSRH AS STRING
DIM rsSRH AS DAO.Recordset
DIM fldMBR as Access.Field
DIM stSRHPART AS STRING

    stSRHPART = "LIKE '* " & REPLACE( TRIM( txtSearch ), " ", "*" ) & "*'"      'build a component that will appear in every query part

    FOR EACH fldMBR IN MAIN.Fields
        IF stSRH = "" THEN
            stSRH = "SELECT * FROM MAIN WHERE " & fldMBR.NAME & stSRHPART    'create the first line of the query
        ELSE
            stSRH = stSRH &  " UNION SELECT * FROM MAIN WHERE " & fldMBR.NAME & stSRHPART     'create subsequent lines of the query
        END  IF
    NEXT fldMBR

    stSRH = stSRH & ";"        'tack on a trailing semicolon to close the query

    SET rsSRH = CurrentDB.OpenRecordset( stSRH, dbOpenDynaset )          'open a recordset using the query just built
...

At this point, your recordset is open to a list of only those items that match your criteria. What it does is synthesize a UNION query that searches through all fields in MAIN for the compounded search string. If you wanted, you could adapt this to a more general subroutine where MAIN would be one parameter and txtSearch would be the other one. However, that would be more complex and I didn't try to shoot from the hip for that case.

LIMITATIONS: You cannot have more than 32 fields in MAIN because of the Access limit on UNION queries.

EDIT: Added a space in front of the UNION because of possible syntax issues without it.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:44
Joined
May 7, 2009
Messages
19,169
i made new function and called it on your Query.
 

Attachments

  • test.zip
    1.2 MB · Views: 376

Sun_Force

Active member
Local time
Tomorrow, 06:44
Joined
Aug 29, 2020
Messages
396
i made new function and called it on your Query.

You have a flow in your function.
If one of the keywords doesn't have a hit, the other one is not searched.
If you search for Fire Blue nothing is found.

It's OK if it's what OP is looking for. But normally search functions doesn't work this way.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:44
Joined
May 7, 2009
Messages
19,169
You have a flow in your function.
If one of the keywords doesn't have a hit, the other one is not searched.
If you search for Fire Blue nothing is found.
that was the point raised on post#1.
all of the keyword Must be present on the result.
 

Users who are viewing this thread

Top Bottom