Splitting a field in a query

  • Thread starter Thread starter Wendy
  • Start date Start date
W

Wendy

Hi All

I have a name field in my query with Smith, John Charles. I need to know
how to split it on the comma and spaces so I get 3 fields Smith John
Charles. I receive the data in this format so cannot change it at source.
Can someone point me in the right direction please?

Thanks in advance

Wendy
 
Wendy,
Working on the assumption that your names all come in the format of
"LastName, FirstName MiddleName", the following should work for you. It's a
bit long winded, but you ought to be able to just copy and paste it into the
sql view of a new query -- taking care to change the field name and table
name appropriately.

SELECT Left([nametest],InStr([nametest],',')-1) AS LastName,
Left(Mid([nametest],InStr([nametest],',')+2),InStr(Mid([nametest],InStr([nam
etest],',')+2),' ')) AS FirstName,
Mid(Mid([nametest],InStr([nametest],',')+2),Len(Left(Mid([nametest],InStr([n
ametest],',')+2),InStr(Mid([nametest],InStr([nametest],',')+2),' ')))+1) AS
MiddleName
FROM NamesTest;
 
Thanks Lynn it works fine
Wendy said:
Hi All

I have a name field in my query with Smith, John Charles. I need to know
how to split it on the comma and spaces so I get 3 fields Smith John
Charles. I receive the data in this format so cannot change it at source.
Can someone point me in the right direction please?

Thanks in advance

Wendy
Click to expand...
 
Back
Top