Soundex Search

[alphabetsoup]

I'm pretty sure Access is going to be the death of me today.



This is what I have...a query that pops up and ask for student's last name and then provides the requested information when one enters a last name of Smith.

Problem...instructor mistypes student's last name and finds zero results. They typed in Smyth.

Fix...(besides just typing in the name correctly)....A query that uses a soundex search.

I started here, http://www.access-programmers.co.uk/...d.php?t=173710, and downloaded the file and read through the instructions. I can't get it to work...but then again, I'm pretty new to this and maybe I'm missing some important steps. I've read this, http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx, and what I'm reading makes sense...but for the life of me I don't know where to start.

Is there a step by step process or a very basic example somewhere? I know soundex in general is more advanced, but with decent instructions I can do pretty much anything.

I've been in Access way too much the last couple of weeks and if I see one more compile error or anything else that tells me something I did isn't working...I might cry. Thank you!

[TheShabz]

I'm not familiar with soundex so my reply may not be the most useful if you're set on using it. What I would do in this case is instead of having a query pop-up, I would create a form with a search-as-you-type textbox that populates a combobox or a listbox with possible matches, allowing the user to select one and proceed with whatever else needs to happen.

If the teacher was looking for Smith but Typed in Smyth, all that would need to happen is for him/her to backspace a couple letters to narrow down the possible last names to all that started with "sm" then scroll down until the name was found.

Just another option. Depends on how you want to proceed. Hope you find more info on the soundex though.

[alphabetsoup]

That sounds like a good idea actually. However, again I am at a loss on how to start with something like that.

Create a form, combo box which would populate another section of the form (below the combo box) with student names and information. Makes sense, but I am not for sure how to create a type as you go sort of serch process.

Can you or another poster give me some more information or point me in the direction of help? Thank you!

Edit - Okay, I have a form created. I have a combo box that populates a subquery. Yay it works! NOW I need it to populate in a "type as you search" method. So I have what I believe is the first step done, How do I get to the second step?

[orange]

I saw this thread and thought I'd try to use Soundex algorithm found in Allen Browne's materials.
see http://allenbrowne.com/vba-Soundex.html

I downloaded the Soundex and getSoundex functions.
I then took a table Customers (from NorthWind) and added 2 fields
CompSoundex text and ContactSoundex text

I then ran an Update query to set the Soundex value for the CompSoundex and ContactSoundex

UPDATE Customers
SET Customers.CompSoundex = soundex([companyname])
, Customers.ContactSoundex = soundex([contactName]);

Then I tried to find a record based on a Soundex search

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactSoundex, Customers.ContactName
FROM Customers
WHERE (((Customers.ContactSoundex) Like "*" & soundex("André Fonsy") & "*"));

The result of the query using Soundex is attached as a jpg.

I hope this is helpful to you.

Good luck.