Results 1 to 13 of 13
  1. #1
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22

    Soundex Searching

    I am working with Access 365 and am having a difficult time trying to do a partial search for some data in my query. I am using Query Design to try and pull up data without having to know the entire name of the Agency or Business. I have tried entering: Like "*" & [Enter all or part of business name:] & "* for my search criteria, but it keeps changing Like to ALike and when I enter the first word of a business name that I know is in the table it returns nothing.



    Any guidance would be greatly appreciated.

    Thank you in advance.

    DaKetch

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    This is a split database?

    Found this: http://codevba.com/msaccess/sql_alike.htm#.XLeF4maWyCg

    Check your db Options for SQL Server Syntax compatibility: File » Options » Object Designers » Query Design

    I do not experience this with my Access2010 (I am not using 365).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22
    It is not a split database. This is what the Select statement actually looks like:

    SELECT tbl_AgencyInfo.Agency_Online, tbl_AgencyInfo.Agency_Name, tbl_AgencyInfo.Agency_Address, tbl_AgencyInfo.Agency_City, tbl_AgencyInfo.Agency_State, tbl_AgencyInfo.Agency_Zip, tbl_AgencyInfo.Agency_Phone_1, tbl_AgencyInfo.Agency_Phone_2, tbl_AgencyInfo.Agency_Fax, tbl_AgencyInfo.Agency_Contact_Last_Name, tbl_AgencyInfo.Agency_Contact_First_Name, tbl_AgencyInfo.Agency_Website, tbl_AgencyInfo.Agency_Resume, tbl_AgencyInfo.Agency_CoverLetter
    FROM tbl_AgencyInfo
    WHERE (((tbl_AgencyInfo.Agency_Name) ALike "*" & [Enter all or part of Agency Name:] & "*"));

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Did you look at DB Options?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22
    I have even tried: ALike Chr(34) & "*" & [Enter all or part of Agency Name:] & "*" & Chr(34)

  6. #6
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22
    Really dumb question, what would I be looking for in the DB Options?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Described in post 2.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22
    I am so Sorry, I didn't see that. My Bad!

  9. #9
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22

    Copy of Database

    I have attached a copy of my database. There is only one query. If you look at the tbl_AgencyInfo table, you will be able to see the names of the agencies I am using. Do not have the query attached to a form yet as I wanted to build and test it before I attached it to the form.

    I am at a loss on this one. I did what you said and have tried everything I found on the internet and it doesn't seem to work.JobSearchDatabase.zip

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Hold down shift key while opening your db. Now you can change the Option setting back to default. Fix query, LIKE will stay LIKE.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22
    I figured it out. I was using the wrong wild card. I switched to the % and it worked.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, that is SQLServer compatible syntax as described in link in post 2. If you want to use Access compatible follow instructions in post 10.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Just to clarify your title, this is not a soundex search.
    Access does not have a native soundex function.
    There are many examples online, heres one...http://allenbrowne.com/vba-Soundex.html

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

Similar Threads

  1. Soundex() linking
    By Kundan in forum Forms
    Replies: 13
    Last Post: 02-28-2019, 09:40 PM
  2. similar to a soundex search?
    By Jen0dorf in forum Access
    Replies: 2
    Last Post: 08-09-2017, 10:54 AM
  3. Soundex search not repeating on fail
    By Jen0dorf in forum Access
    Replies: 10
    Last Post: 07-28-2017, 05:33 AM
  4. Soundex query code
    By Jen0dorf in forum Access
    Replies: 9
    Last Post: 04-14-2016, 02:01 PM
  5. Soundex Search
    By alphabetsoup in forum Queries
    Replies: 3
    Last Post: 10-05-2011, 10:47 AM

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