Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Oct 2010
    Bismarck, ND
    Thanked 0 Times in 0 Posts

    Question MS Access 2007-Extracting Variable Length Text From A Field

    I have been given an existing Access 2007 database to modify. Being somewhat new at this, I am having a bit of trouble & would really appreciate some help.

    Situation: The existing db has a text field containing a name in the format: LastName<space>FirstName. LastName can be any length, as can FirstName (neither are a fixed length). Sometimes the space between the names will actually be 2 spaces if the typer had a heavy thumb.

    Requirement: Create 2 new fields in the db; one for LastName and the other for FirstName.

    Problem: how to create a query or queries to:
    1. extract the LastName only from the existing text field and populate the new LastName field.
    2. extract the FirstName only from the existing text field and populate the new FirstName field.

    I'm sure there's a way to do this. I've just not been successful finding it.

    Thank you for any suggestions that enable me to get this done - without manually retyping 8000+ names.

    Sorry to take up time & space. I just found an answer to my problem via another source. No need to respond, but thank you for looking.
    Last edited by coldstart; 2011-07-14 at 14:21. Reason: Found an answer elsewhere.
    Bismarck ND

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,455 Times in 1,324 Posts

    See graphics.
    Basically I did a query to test my logic then I changed it into an Update Query and ran it.
    Remember to add the new First & Last name fields before running the Update Query.
    When done and verified you can delete the Full Name field.

    Note: This will NOT handle Middle Name/Initials or Honorifics {Dr. Mr. etc.} or Generations {Jr. III, etc.}.
    Attached Images Attached Images
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

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