Results 1 to 5 of 5
  1. #1
    UTS is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Downunder
    Posts
    17

    Split Firstname/Secondname when sometimes only firstname

    N00B here,

    I have a field GIVEN_NAMES with first name and middle name separated by a space. Some entries have only the first name.



    So, running this query

    FirstName = Left([GIVEN_NAMES]), " ")

    doesn't work, as it leaves the output blank when it cannot find the separating space.

    Any ideas how to separate these?

    In addition, if I run this as an update query, it doesn't work, but also places a "0" in the GIVEN_NAMES field. Lucky I backed up my table!

    Cheers,

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Have you looked into using an IIF(InStr([GIVEN_NAMES]," "),...
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    UTS is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Downunder
    Posts
    17
    Have you looked into using an IIF(InStr([GIVEN_NAMES]," "),...

    Could you please expand on this? I thought IIF was more a logical function.

    Cheers,

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Try:
    FirstName = IIF(InStr([GIVEN_NAMES]," "),Left([GIVEN_NAMES]), " "),[GIVEN_NAMES])
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    UTS is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Downunder
    Posts
    17
    Still could not get it to work. It pulls out the FirstName OK, ONLY when there is a SecondName present, i.e. two words separated by a space. But when there is only a single word (FirstName) it doesn't extract it as there is no space.

    Anyway, I managed to get the job done by pulling out the FirstName and then deleting those records, which left the single FirstName in the GIVEN_NAMES field, which I then copied across to the SecondName field.

    I know - a bit messy - but it worked.

    Cheers,

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

Similar Threads

  1. FirstName + LastName
    By mehulkar in forum Access
    Replies: 1
    Last Post: 07-28-2011, 01:40 PM
  2. Split Name
    By Blessy clara in forum Access
    Replies: 2
    Last Post: 05-03-2011, 03:12 PM
  3. Concatenate firstname + lastname
    By Dega in forum Access
    Replies: 2
    Last Post: 08-11-2010, 04:58 PM
  4. When to split
    By Mclaren in forum Database Design
    Replies: 4
    Last Post: 07-07-2010, 07:25 AM
  5. ULS gone after split.
    By evander in forum Database Design
    Replies: 3
    Last Post: 07-06-2010, 11:49 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 - Senior Forums