Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    29

    Unanswered: Another deleting question similar to the states one.

    The information company that i send my names and addresses to now only want one name for the "First Name" Field

    but this is the case for most of them

    First Name | Last Name
    -------------------------------------
    Jon & Maria | Barker

    or


    First Name | Last Name
    -------------------------------------
    Jon F & Maria | Barker


    What i need to do is in my "Table", the column of "First Name" i need for Access to delete everything AFTER AND INCLUDING the "&"

    so it would look like:

    First Name | Last Name
    -------------------------------------
    Jon F | Barker

    i hope i explained it good, i did something similar with deleting 2 letter states but im not sure how similar it is

    Thank you for all the help guys i really do appreciate it.

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    This will do it:

    UPDATE [Your Table] SET [Your Table].[First Name] = Left([first name],InStr([first name],"&")-2)
    WHERE ((([Your Table].[First Name]) Like "*&*"));

    The reason for the WHERE clause is in case you have some records with only one name or in case you run the query twice.

    TD

  3. #3
    Join Date
    Oct 2004
    Posts
    29
    alright i got that... theres still some left that didnt have the "&" and are just 2 word names...

    what would i be doing to make them just one word name?
    Last edited by idioteque; 12-15-04 at 19:02.

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    You can use this, but the example that you gave with Jon F will become Jon.

    UPDATE [Your Table] SET [Your Table].[First Name] = Left([first name],InStr([first name]," ")-1)
    WHERE ((([Your Table].[First Name]) Like "* *"));

    TD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •