Splitting data in 1 field

[Guest]

I have a name field that looks like, John Doe. I would like to split this
field into last name and first name fields. Can someone tell me how I would
do this?

Thanks.

 

[Guest]

Lisa,

If you are sure that each record only contains two names, you could add the
Last_Name, and First_Name fields to your table, then write an update query
that looks something like:

Update yourTable
Set Last_Name = Mid([Name], instr([Name], " ") + 1),
First_Name = Left([Name], instr([Name], " ") - 1
WHERE instr([Name], " ") > 0

Unfortunately, data is rarely this simple.

You will probably have some where there is only one part of the name, and
others where the [Name] field contains a prefix or suffix. You might want to
start by querying the database to identify those that have more than one
space, and correct these records. The query might look like:

SELECT [Name] FROM yourTable
WHERE LEN([Name]) - LEN(Replace([Name], " ", "")) > 1

HTH
Dale

 

[fredg]

I have a name field that looks like, John Doe. I would like to split this
field into last name and first name fields. Can someone tell me how I would
do this?

Thanks.

Well it's not as simple as you may think.

If the names are ALL like FirstName space LastName you can use:
FirstName = Left([FullName],InStr([FullName]," ")-1)
LastName = Mid([FullName],InStr([FullName]," ")+1)

Then how would you split names like
Jo Ann van der Meer
or
Hans von Clauswitz
etc.?

 

[Carl Rapson]

I have a name field that looks like, John Doe. I would like to split this
field into last name and first name fields. Can someone tell me how I
would
do this?

Thanks.

You can use the Split function:

Dim ret() As String
ret = Split("John Doe", " ")

The variable 'ret' will be an array of strings split based on the delimiter
you specified (a space, " "). So:

ret(0) = "John"
ret(1) = "Doe"

Carl Rapson

 

[Guest]

Parsing names is one of the two most difficult tasks you can take on. The
other is parsing addresses. In either, there are too many variable
circumstances for you to be able to catch every thing. For example, How
would you parse these:

Fredrick J. Von Whomper III
B. J. Hammond

Alfonse McIntyre, Phd.

Here is the real solution. Change your table structure to include a field
for each element of the name that you want to store. Create a query that
parses them to the individual fields as best you can. Go back an manually
modify any that did not get modified correctly.

Now you have something you can work with. It is much easier to concatenate
than to parse.

 

[Guest]

I have the same problem. However I am linking my table to Quickbooks using a
read/write ODBC driver. If I were to insert new columns as instructed above,
is it going to make that change in QuickBooks too?

 

[Guest]

No, it will not.