Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unanswered: making one field two feilds (First Name Last Name)

    I have a couple of feilds (Full name and Zip code) I would like to seperate into seperate feilds. The Full name feild I would like to split into two fields by the space. I would like to split the Zip code into the two fields the first 5 digits, and the last four by the -. This is my first posting so forgive me any transgressions.

    Cheers
    parker

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's just text parsing:

    First Name: Left(Field, InStr(1, Field, " ") - 1)
    Last Name: Right(Field, (Len(Field) - InStr(1, Field, " ")))
    First Zip: Left(zip, 5)
    Second Zip: Right(zip, 4)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    In what level do you want to carry out this task? Form or Table?

    For Starters.....


    Code:
    Dim FirstName As String
    Dim LastName As String
    Dim ZipFirst As String
    Dim ZipLast As String
     
    FirstName = Left$(Me.myNameTextBox, Instr(Me.myNameTextBox, " ") - 1)
    LastName = Mid$(Me.myNameTextBox, Instr(Me.myNameTextBox, " ") + 1, Len(Me.myNameTextBox))
     
    ZipFirst = Left$(Me.myZipCodeTextBox, Instr(Me.myZipCodeTextBox, "-") - 1)
    ZipLast = Mid$(Me.myZipCodeTextBox, Instr(Me.myZipCodeTextBox, "-") + 1, Len(Me.myZipCodeTextBox))




    Whoops....Teddy beat meto it
    Last edited by CyberLynx; 06-21-04 at 16:13.

  4. #4
    Join Date
    Jun 2004
    Posts
    5
    First off, Thank you i had no idea what kind of responce time to expect. This is amazing. I have read through some of the history. Forgive my naive on this matter. My experince with access is mostly on the data quering for sales reports and merge purge for mailings.

    I will be doing this on the table level, i guess. Would i do this through a query, like an update query?
    Would this be how i would apply that
    Where [zip] is like "*-*" set [zip5] to Left(zip, 5) .

    THe feild name that has the zip plus 4 is zip and i would like the first 5 in the zip to be in the zip5 feild.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Update query indeed.

    UPDATE yourTable
    SET firstname = Left(Field, InStr(1, Field, " ") - 1), lastname = Right(Field, (Len(Field) - InStr(1, Field, " ")))


    etc etc
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jun 2004
    Posts
    5
    Thank you so much it worked out perfectly and now i have a new tool to try to figure out how to use. Cheers
    parker

Posting Permissions

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