Cleaning up a data field in Access

[Guest]

I have a file from a customer, who has formated the name field like:
"John***Doe", how can I extract the first name and the last name and break
them up, putting them in the proper first name and last name fields?

 

[Jason Lepack]

I have a file from a customer, who has formated the name field like:
"John***Doe", how can I extract the first name and the last name and break
them up, putting them in the proper first name and last name fields?

In a query:
SELECT
Left([SillyName],InStr(1,[SillyName],"***")-1) AS FName,
Mid([SillyName],InStr(1,[SillyName],"***")+3) AS LName
FROM yourTable;

Cheers,
Jason Lepack

 

[John W. Vinson]

On Tue, 6 Feb 2007 10:05:02 -0800, Jean J <Jean

I have a file from a customer, who has formated the name field like:
"John***Doe", how can I extract the first name and the last name and break
them up, putting them in the proper first name and last name fields?

Do the names *all* have exactly three asterisks? Or is there a
variable number - I can imagine a file with first names "asterisk
filled" to a constant length?

Jason's query is right on the mark if it's always *** - but may need
tweaking if it's not. Just for safety's sake I'd use a criterion

LIKE "*[A-Z][*][*][*][A-Z]*"

to include only those fields which have a letter, three asterisks, and
another letter somewhere in the name field.

Just from curiosity: how would names like "Billy Bob Barnes" or "Ralph
von Wau Wau" be entered in this system?

John W. Vinson [MVP]