split full name(last, first) to last name and first name fields

Discussion in 'Microsoft Access Queries' started by Guest, Jan 2, 2006.

  1. Guest

    Guest Guest

    I'm an Access novice. I've found answers like UPDATE table SET
    lastname=Left([fullname]],InStr(fullname],",")-1 I vreated a Query, changed
    it to Update Query and put that code in "Update to" field of lastname but all
    I get is a 0(zero) in last name field. Should code go in "Criteria" field ?
    I'm missing something.
     
    Guest, Jan 2, 2006
    #1
    1. Advertisements

  2. Show us sample data for your fullname values.

    The expression goes in the "Update To:" field.

    I do note a syntax error, though, in the expression that you posted. Change
    it to this:

    Left([fullname],InStr([fullname],",")-1)

    --

    Ken Snell
    <MS ACCESS MVP>

    "Access Novice in Confusion" <Access Novice in
    > wrote in message
    news:...
    > I'm an Access novice. I've found answers like UPDATE table SET
    > lastname=Left([fullname]],InStr(fullname],",")-1 I vreated a Query,
    > changed
    > it to Update Query and put that code in "Update to" field of lastname but
    > all
    > I get is a 0(zero) in last name field. Should code go in "Criteria" field
    > ?
    > I'm missing something.
     
    Ken Snell \(MVP\), Jan 2, 2006
    #2
    1. Advertisements

  3. Add this to your "Update to" field in your query.

    Left([FullName],InStr(1,[FullName],",")-1)
    Should give you the LastName - when FullName = LastName, FirstName

    Mid([FullName],InStr(1,[FullName],",")+1)
    Should give you the FirstName - when FullName = LastName, FirstName

    Review your documentation on the InStr function.
    Based on your note, I'm surprised you got the query to run at all. The syntax
    in your example is incorrrect. I mention this as syntax is just about the
    MOST important thing one should look at when debugging.

    Hope this helps.

    Access Novice in Confusion wrote:
    >I'm an Access novice. I've found answers like UPDATE table SET
    >lastname=Left([fullname]],InStr(fullname],",")-1 I vreated a Query, changed
    >it to Update Query and put that code in "Update to" field of lastname but all
    >I get is a 0(zero) in last name field. Should code go in "Criteria" field ?
    >I'm missing something.


    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200601/1
     
    Eric D via AccessMonster.com, Jan 2, 2006
    #3
  4. Guest

    John Spencer Guest

    Two things.
    One you have a syntax error in what you posted.
    Two you may be putting too much in the update to field.

    Field: LastName
    Update: Left([TableName].[FullName],Instr(1,[TableName].[Fullname],",")-1)

    Access Novice in Confusion wrote:
    >
    > I'm an Access novice. I've found answers like UPDATE table SET
    > lastname=Left([fullname]],InStr(fullname],",")-1 I vreated a Query, changed
    > it to Update Query and put that code in "Update to" field of lastname but all
    > I get is a 0(zero) in last name field. Should code go in "Criteria" field ?
    > I'm missing something.
     
    John Spencer, Jan 2, 2006
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. fcarter

    display first letter of first name and last name fields

    fcarter, Oct 8, 2003, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    315
    fcarter
    Oct 10, 2003
  2. Kelvin Beaton

    if I know the last name can I get the first name from the full name?

    Kelvin Beaton, Jul 23, 2007, in forum: Microsoft Access Queries
    Replies:
    11
    Views:
    337
    Kelvin Beaton
    Jul 25, 2007
  3. IrishRed

    First Name Variations - Split MI from First Name

    IrishRed, Dec 1, 2008, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    363
    John W. Vinson
    Dec 1, 2008
  4. CJH
    Replies:
    3
    Views:
    1,186
    John Spencer
    Oct 19, 2009
  5. Iram

    Switch Last Name First Name to First Name Last Name

    Iram, Jun 4, 2010, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    1,278
Loading...

Share This Page