Share via

Split a name field (Lname,Fname) into two fields at the comma

Anonymous
2013-10-29T15:51:08+00:00

I need to split a name field (Lname,Fname) into two fields at the comma. I'm using the design view in a query and updating the fields, but the Split([fieldname],",") results in "Undefined function "Split' in expression". I'm using Access 2003.  Sorry about the unsophistication but I'm not into the SQL stuff.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-10-29T15:59:37+00:00

    I believe that in 2003 the Split() function was not available in Queries, only in VBA code.

    I'd suggest using the builtin functions: update LName to

    Left([fieldname] & ",", Left([fieldname] & ",", ",") - 1)

    and FName to

    Trim(Mid([fieldname], InStr([fieldname], ",") + 1))

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2013-10-29T17:05:18+00:00

    I think John might have meant this:

    Left([fieldname] & ",",Instr([fieldname] & ",", ",") - 1)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-10-29T16:45:15+00:00

    Hi PatriciaPeacock,

    Try the error handling of null value or no comma below, hope it helps.

     IIf(InStr(Nz([Name],0),",")=0,"",Left([Name],InStr([Name],",")-1))

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-10-29T16:27:26+00:00

    Thanks!  The Trim function for the first name worked like a charm. The left, however, didn't update due to a type conversion failure.

    Was this answer helpful?

    0 comments