Word/Sentence Searching With % Matches

[Dreamweaver]

What I am trying to do is split a sentence into words Like

Jocelyne Labylle And Jacob Desvarieux

This would produce 4 words as the and,&,/ Ect are removed

So I end up with:-

Jocelyne
Labylle
Jacob
Desvarieux

If all 4 words are found in a string it returns all matching records plus gets 100%

3 words again returns all matching records plus say 75% and so on

I haven't done a lot with it at the min as the first bit is quite simple IE getting the names Etc. but I can't think for the life of me how I can do the search.

I Have Tried a query With [Names] Like ""*[String Names]*""" but it seems to only work with each letter???

but my main reason for asking here is I don't like reinventing the wheel or at least trying to which will mean hours of Fun but maybe somebody knows of something I could use on the net.

Thanks for any help/Advice.

mick

 

[KKilfoil]

You need ampersands (&) to concatenate strings together, such as
Like '"*"&[String Names]&"*"'

 

[Dreamweaver]

You need ampersands (&) to concatenate strings together, such as
Like '"*"&[String Names]&"*"'

Many Thanks KKilfoil I'll give that a go.


Best Wishes

mICK

 

[Dreamweaver]

Sorry but could't get it working in a query but been diggin and another option may be to use Seek in a recordset.

mick

 

[Dreamweaver]

I've been playing for ages tying different things but it still insists on doing a letter rather word list

Sub Testing()
Dim M_Db As Database
Dim m_rst As DAO.Recordset
Dim StrSQL As String
Dim EntityID As Long
Dim StrFolder As String
        Set M_Db = CurrentDb()
    Set m_rst = M_Db.OpenRecordset("SELECT * FROM tblRecordingArtists WHERE [RecordingArtistName] Like ""*" & "[Toyah Frip]" & "*""")
    m_rst.MoveLast
    m_rst.MoveLast
    Debug.Print m_rst.RecordCount
    
End Sub

It's Gotta be me LOL but this arangment gave me the best results just 99,000 out LOL

 

[Dreamweaver]

Got it working After just a few hours playing But not how I wanted but it works 99,100 Entrys takes 2 Secs

Sub Testing()
Dim M_Db As Database
Dim m_rst As DAO.Recordset
Dim Sp() As String
Dim Wh As String
Dim i As Integer

Sp = Split("Bryan Adams Melanie")
   For i = LBound(Sp) To UBound(Sp)
   Wh = Wh & " OR [RecordingArtistName] Like """ & "*" & Sp(i) & "*" & """"
   Next i
   Wh = Right(Wh, Len(Wh) - 4)
        Set M_Db = CurrentDb()
    Set m_rst = M_Db.OpenRecordset("SELECT * FROM tblRecordingArtists WHERE " & Wh)
    Do While Not m_rst.EOF
    Debug.Print m_rst("RecordingArtistName")
    m_rst.MoveNext
    Loop
End Sub

I will post the Full Function to the code Forum But hopefully somebody has a better way of doing it.

These are the results from above

Trish Adams
Bryan Cranston
Dannon Bryant
Ryan Adams & The Cardinals
Melanie Vesey
Melanie R. Orr
Bryan Fagerstrom
J.B. Adams
Christine Adams
Melanie Gibbs
Peggy Scott-Adams
Bryan Epsom
Ray Bryant
Bryan Hanna
Frank Adamson
Bryan Burdon
Bryan Jardine
Melanie Hawkins
Bryan Matsuura
Bryan Probets
Melanie Bergeron
Al Bryant
Melanie Gerren
J. Adams
Melanie Spore
Adamski
Bryan Adams
Arthur Adams
Cliff Adams Orchestra
Gayle Adams
Oleta Adams
Melanie B
Dora Bryan
Kelle Bryan
Anita Bryant
Melanie C
Bryan Ferry
Adamski Feat. Seal
Bryan Ferry & Roxy Music
Sweeney Todd feat. Bryan Adams
Sub Sub feat. Melanie Williams
Bryan Powell
Melanie Blatt
Ryan Adams
Chicane feat. Bryan Adams
Glen Adams
Bryan Adams Feat. Melanie C
Cliff Adams Singers
Melanie
Melanie G
Bryan Adams & Barbra Streisand
Outsidaz Feat. Rah Digga & Melanie Blatt
Bryan Johnson
Bryan Hyland
Nej Adamson
John L. Adams
Melanie Williams
Bryan Buffington
Bryan Burwell
Patrick J. Adams
Bryan Chafin
Robert Bryant
Melanie Miller
Tom Adams
Katie Adams
Bryan Friday
Zachery Ty Bryan
Bonnie Raitt Feat. Bryan Adams
Anita Adams
Melanie Comarcho
Bryan Forbes
Dee Dee Adams
Ramon Adams
Bryan Adams/Tina Turner
Bryan Coleman
Ames Adamson
Frederica Sorel And Melanie Cohl
Steve Adams
Bryan Law
Melanie Benz
Ashli Adams
Therond Justin Adams
Wi-Fi Feat. Melanie M
Melanie Anneke Conopask
Bryan Shy
Sean O'Bryan
Julie Adams
Eddie Adams
Karis Paige Bryant
Melanie Thornton
Barbara Streisand/Bryan Adams
Ray Adams
Bryan Singer
Uwe Adams
Melanie Hill
Melanie Berry
Bryan Adams/Rod Stewart/Sting
Dave Adams
Melanie Walsh
Rob Adams
Bryant Gumbel
Ornella Bryant
Jay Adams
Melanie Tonello
Granville Adams
Hayden Adams
Bryan Cuprill
Melanie Angel
Melanie Lynskey
Sydney Bryan
Rachel McAdams
Bryan Holly
Bryant Grizzell
Cleptomaniacs Feat. Bryan Chambers
Bryan Thomas
Ray Bryant Combo
Melanie LaPatin
Bryan Elliot
Evan Adams
Adamski's Thing
Christopher Adamson
Melanie Tolbert
Bryan Anthony
T.J. Adams
Fireflies Feat. Ritchie Adams
Yolanda Adams
Melanie Maurin
Andrew Adamson
Natalia D. Adams
Dayna Adams
Bryan Callen
Melanie C Feat. Lisa 'Left Eye' Lopes
Joe Bryan
Molly Bryant
Bryan Lukasik
Melanie Kilburn
Brooke Adams
Simon Adams
Melanie Gorombol
Melanie Cotter
Abigail Adams
Erik Adams
Cindy Adams
Bryan Dick
Christopher Adams
Bryan Ling
Bryan O'Hair
Christina Adams
Darian Bryant
Connie Adams
Christopher Bryan
Bryan Jamerson
R.J. Adams
Barry Adamson
Melanie Garside
Melanie Lewis
Joe Adams
Maurice Bryant
Michael Adamshick
Lillian Adams
Nate Adams
Bryan Carmen
Bryan Ross
Robbie Bryan
Sara Bryan
Sabrina Bryan
Melanie Chartoff
Jane Adams
Bryan A. Robinson
Melanie A. Gage
Libby Adams
Bryan Clark
Blake Michael Bryan
Melanie Simpson
Bryan Richard Deehring
Bryan Marshall
Sharon Bryant
Melanie Williams And Joe Roberts
Bryan Birge
Bryan Gordon
Melanie Perks
Melanie H. Gassaway
Bryan Hearne
Jess Bryan
Kristin Adams
Misty Adams
Eboni 'Chrystal' Adams
Bryan Adams, Rod Stewart And Sting
William Bryant II
Adamski Feat. Jimi polo
Bryan Donoghue
John 'B.J.' Bryant
Bryan Kirkwood
Bryan Renfro
Lindsay Bryan

Thanks
Mick