Soundex query code

[Jen0dorf]

HI



I’m confused as usual an am experimenting with Alan Brownes Soundex.

http://allenbrowne.com/vba-Soundex.html

I’m following his example and have created a query using his select query with the criteria

soundex([Forms].[Form1].[txtName])

My form is called frmSurnameSearch and my field is called txtSurname

So I changed the criteria to

soundex([Forms].[frmSurnameSearch].[txtSurname])

but when I run it I get a box asking me to input criteria.

via google this code is suggested

=Forms!FormName!FieldName

but that doesn’t work either

I have created the module for soundex as per the website

I’m assuming the code is correct?

any suggestions appreciated

thanks

Ian

[pbaldy]

Never used it, but Allen's stuff is usually pretty good. Is the form open at the time the query runs?

[Jen0dorf]

Hi

yes it is, no doubt it's me being dense, I'm surprised that Access has no built in Soundex but a google search shows very little information.

I'll carry on experimenting

cheers

Ian

[orange]

Ian,

I saw your earlier post re soundex query with a friendly message if no results.

I tried a form and a know table/query and mocked it up.

In my sample I am using my table (data_dictionary) which is documentation of all fields in all tables in this database. I am using Allen Browne's Soundex function.
I am attaching some sample data from my table. And a jpg of the form involved.

It may be useful.
I could mock up a sample using your table/query names if you can supply a little data.


Good luck

[Jen0dorf]

Hi

I've stripped out my database and attached it - two forms

If you just press search or type one letter into the box then you just see an empty form I need a nice message

Raman 256 was kind enough to suggest code

If dcount("*","qsQuery") =0 then
    MsgBox "No results"
else
  Docmd.openquery "qsQuery"
end ifqsClassRoster

But Despite spending most of the day on it - and night - (it's now 3am) I've failed to work out how to implement that code.

It seems to be one step forward and two back

Your mock up looks great but I'm a bit to tired to work it all out!!

anyway if you have time to take a look I'd be grateful

thanks

Ian

[orange]

It's late here too.
What's wrong with your code?
Seems to work ok with surnames????

10:30 -- I'm signing off, see you in the AM

[Jen0dorf]

Hi

perhaps I'm worrying about nothing then?? Am I over complicating it all trying to cover every eventuality?

If the user was to type in just the letter "S" and then presses the search button an empty form appears, I thought this was perhaps a bit confusing? If they type in a possible name say Smith it works Ok because there is a match.

Can I rely on the end user to type a full name?

It might be enough to add a text field to the search form saying "Please Enter a Name of 4 characters or more?

thanks

Ian

[orange]

Ian,

It could be that you are "over-expecting" the ability of Soundex to find matches. If you look at my sample in post 4, you'll see there is quite a range of what is returned - probably more than expected. On the other side of the results, I had to input more than expected to get a result.
eg to find Catherine, I had to supply "Cather" before I got a result.

If you want to increase the probability of finding records, I would suggest adding additional criteria.
For example, if you want Surnames starting with "S" , you could keep the current soundex and add
OR Surname Like "S"& "*".

In your sample it would be along the lines of (this is just pseudo code for concept)

soundex(baptism.surname) = soundex(your form textbox) Or baptism.surname like  & your form textbox & "*"

You may want to look at a couple of search examples I added (these deal with partial string matching)

https://www.accessforums.net/showthread.php?t=43055
https://www.accessforums.net/showthread.php?t=49804

Good luck.

UPDATE:

I modified my form to also display the matching records count. I used your tbl_Baptism and Tbl_place.
Attached is a jpg showing the number of records matching a single "s". 81 records.

Then using "se", the result set was reduced to 16 records.

Also, I don't use macros. Your output/results with the links seems very nice to open the detail record(s).

My sql for the query is based on yours. I just separated the soundex( of the text1 control) to resolve a syntax issue I had.

    Dim SQL As String
20  mparm = Soundex(Me.Text1)
30  SQL = "SELECT tbl_Baptism.BaptismID, tbl_Place.Place, tbl_Baptism.FicheNo, tbl_Baptism.BirthDate," _
          & " tbl_Baptism.DateOfBaptism, tbl_Baptism.YearOfBaptism, tbl_Baptism.ChildsName, tbl_Baptism.Surname," _
          & " tbl_Baptism.Sex, tbl_Baptism.Parents, tbl_Baptism.Abode, tbl_Baptism.Occupation, tbl_Baptism.Church," _
          & " tbl_Baptism.RefNo, tbl_Baptism.PageNo, tbl_Baptism.EntryNo, tbl_Baptism.Minister, " _
          & " tbl_Baptism.FullDateOfBaptism " _
          & " FROM tbl_Place INNER JOIN tbl_Baptism ON tbl_Place.ID = tbl_Baptism.[fk_PlaceId] " _
          & " WHERE soundex([surname])= '" & mparm & "' OR   Surname Like  '*" & Me.Text1 & "*'" _
          & " ORDER BY tbl_Baptism.FullDateOfBaptism;"

[Jen0dorf]

Hi

thanks for that, Seems Soundex is more complicated than I thought!

cheers

Ian

[orange]

Ian,

Not sure it's more complicated. I think it may not do what you might expect. At least not as well as one might think.
There are various articles on Fuzzy Matching and most start with soundex as a base and refine/extend the algorithm.

I worked with the person who had designed the NUANS name search and refined it. The algorithms can get quite complex.

You may find this interesting at least as a reference.

https://en.wikipedia.org/wiki/Approximate_string_matching

More here at UtterAccess