Results 1 to 4 of 4
  1. #1
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    table lookup issue

    Hi

    I have decided to follow the advice given re a soundex search and create a field in my baptism table named SDXsurname.

    so I need to take the value from a field named surname in the baptism table ( format short text) and pass it through Allen Brownes Soindex module and then write the result into the table field SDXSurname which again is in teh baptism table.

    So my first step was to create a query based on my baptism table which contains the field Surname and the field SDX surname.

    I named it qry_sdx_baptismSurname



    Under the criteria for the SDXSurname field I'm trying to populate I added

    Code:
    SELECT tbl_Baptism.SurnameSDX, tbl_Baptism.Surname
    FROM tbl_Baptism
    WHERE (((tbl_Baptism.SurnameSDX)=Soundex([tbl_Baptism].[Surname])));
    I then opened my baptism table and under the field

    SurnameSDX selected lookup wizard and selected the query

    qry_sdx_baptismSurname

    I get the error

    No Valid Fields can be foumd in qry_sdx_baptismSurname. You may have selected a query that uses the table you are adding the lookup column to,

    Clearly I am making a fundemental error

    any help as usual appreciated

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Is the field name SurnameSDX or SDXSurname?

    Maybe:

    UPDATE tbl_Baptism SET SurnameSDX = Soundex([Surname]);


    Going forward, whenever creating a new record in tbl_Baptism or editing the surname value, include running the Soundex() function as part of process.
    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
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    soundex update

    Apologies the field name in the table tbl_baptism is SurnameSDX

    I'm a bit lost on the update although I do understand the code in the line/

    I changed my query to

    Code:
    UPDATE tbl_Baptism SET SurnameSDX = Soundex([Surname]);
    I then went to table design and selected lookup wizard as the option and selected my query.

    However I got the same error as before.

    Sorry if I'm being a bit slow but am I missing something?

    thanks

    Ian

    Quote Originally Posted by June7 View Post
    Is the field name SurnameSDX or SDXSurname?

    Maybe:

    UPDATE tbl_Baptism SET SurnameSDX = Soundex([Surname]);


    Going forward, whenever creating a new record in tbl_Baptism or editing the surname value, include running the Soundex() function as part of process.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This has nothing to do with lookup. This suggestion is to actually populate a field with the value calculated by the Soundex() function.

    That is what an UPDATE sql action statement would accomplish. Run the action and the values will populate into tbl_Baptism.
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-02-2017, 03:49 PM
  2. Combobox record lookup and .oldvalue compatibility issue.
    By MatthewGrace in forum Programming
    Replies: 3
    Last Post: 08-02-2014, 10:23 PM
  3. Replies: 1
    Last Post: 07-17-2014, 05:51 PM
  4. Lookup wizard & add-ins: vba issue
    By rogueknight in forum Access
    Replies: 0
    Last Post: 03-28-2012, 02:56 PM
  5. Autofill / lookup issue
    By bertollini in forum Access
    Replies: 9
    Last Post: 02-24-2012, 06:08 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