Split a Field into two fields

S

Stewart Saathoff

Hello,

I have to compare two databases. The first stores names in the format
"FirstName LastName" in a single field. The Second Table stores the Values
in this format: "LastName, FirstName" (with the comma)

Is there any way that I can take the values in the second table, split them
into two fields and then combine them back to match the "FirstName LastName"
format?

Thanks
Stewart
 
J

John Vinson

Hello,

I have to compare two databases. The first stores names in the format
"FirstName LastName" in a single field. The Second Table stores the Values
in this format: "LastName, FirstName" (with the comma)

Is there any way that I can take the values in the second table, split them
into two fields and then combine them back to match the "FirstName LastName"
format?

Try:

Trim(Mid([namefield], InStr([namefield], "," + 1) & " " &
Left([namefield], InStr([namefield], ",") - 1)

You now see why most serious developers use separate FirstName and
LastName fields!
 
S

Stewart Saathoff

I always use separate fields for FirstName and LastName. I am having to
write a database that compares information from two different databases
created by two different vendors. You want to hear the really terrible
thing? One of the Vendors is the United States Government. The application
queries a set of tables that was created to maintain a list of, get this,
terrorists. We have to create an app that compares the governments list
against our clients customer lists and the government keeps the entire name
in one field. If you want to see what I am talking about, download the list
here:

http://www.treas.gov/offices/eotffc/ofac/sdn/

Thanks for the help.


John Vinson said:
Hello,

I have to compare two databases. The first stores names in the format
"FirstName LastName" in a single field. The Second Table stores the Values
in this format: "LastName, FirstName" (with the comma)

Is there any way that I can take the values in the second table, split them
into two fields and then combine them back to match the "FirstName LastName"
format?

Try:

Trim(Mid([namefield], InStr([namefield], "," + 1) & " " &
Left([namefield], InStr([namefield], ",") - 1)

You now see why most serious developers use separate FirstName and
LastName fields!
 
T

t.y.

And I need to total the sales for each month.
-----Original Message-----
I have a simmilar situation. I need to separate
SalesTotals into SalesTotalsbyMonth.

I have:
Date Sales Rep TotalSales

I need:
Sales Rep JanuaryTotalSales FebruaryTotalSales etc.

Please help!
-----Original Message-----
names
in the format
"FirstName LastName" in a single field. The Second Table stores the Values
in this format: "LastName, FirstName" (with the comma)

Is there any way that I can take the values in the second table, split them
into two fields and then combine them back to match the "FirstName LastName"
format?

Try:

Trim(Mid([namefield], InStr([namefield], "," + 1) & " " &
Left([namefield], InStr([namefield], ",") - 1)

You now see why most serious developers use separate FirstName and
LastName fields!



.
.
 
J

John Vinson

One of the Vendors is the United States Government. The application
queries a set of tables that was created to maintain a list of, get this,
terrorists. We have to create an app that compares the governments list
against our clients customer lists and the government keeps the entire name
in one field.

Oh, good Lord.

At the VERY VERY least this database should have a one-to-many link
from individuals to the aliases under which they are known, not to
mention variant spellings.
 
J

John Vinson

I have a simmilar situation. I need to separate
SalesTotals into SalesTotalsbyMonth.

I have:
Date Sales Rep TotalSales

I need:
Sales Rep JanuaryTotalSales FebruaryTotalSales etc.

This is actually a quite different problem.

Take a look at "Crosstab" in the online help; a Crosstab query will do
exactly what you request.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top