Results 1 to 4 of 4
  1. #1
    alphabetsoup is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2011
    Posts
    4

    Soundex Search

    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!

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    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.

  3. #3
    alphabetsoup is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2011
    Posts
    4
    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?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. search
    By simba in forum Access
    Replies: 12
    Last Post: 08-23-2011, 07:13 PM
  2. Replies: 4
    Last Post: 03-17-2011, 06:17 AM
  3. Search Log!!!!!!!!!?!?!?!?!?
    By JG1992 in forum Queries
    Replies: 2
    Last Post: 03-13-2011, 03:09 PM
  4. help in search
    By Nokia N93 in forum Programming
    Replies: 5
    Last Post: 01-14-2011, 01:49 PM
  5. Replies: 2
    Last Post: 08-31-2010, 08:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums