Splitting a field in a query

[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

 

[Lynn Trapp]

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;

 

[Wendy]

Thanks Lynn it works fine

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

 

[Lynn Trapp]

You're welcome

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm