How to convert 1 name field to two

[Guest]

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.

 

[Guest]

Assuming that there is a comma between the two values
Try this

For John
Left([FieldName],Instr([FieldName],",")-1)

For Smith
Mid([FieldName],Instr([FieldName],",")+1)

 

[Guest]

If your names are like "John,Smith", something like the following will work
in a query once you plug in the proper table and field names. However if they
have middle names, spaces around the comma, on missing commas, well.......

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;

 

[Guest]

Thanks Jerry,

Will this work on the whole "more than 10K" names?

If your names are like "John,Smith", something like the following will work
in a query once you plug in the proper table and field names. However if they
have middle names, spaces around the comma, on missing commas, well.......

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.

 

[Guest]

Thanks Ofer, but will this convert all the names in one stroke?

Assuming that there is a comma between the two values
Try this

For John
Left([FieldName],Instr([FieldName],",")-1)

For Smith
Mid([FieldName],Instr([FieldName],",")+1)

--
\\// Live Long and Prosper \\//
BS"D

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.

 

[Guest]

Sure. However there could be problems if some, even one, record doesn't have
a comma in it. Also if someone swapped around the first and last names.
Middle initials and middle names also could present problems.

But if the data is clean and formatted as you say, 50K records or more
should work OK if not a little slow.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks Jerry,

Will this work on the whole "more than 10K" names?

If your names are like "John,Smith", something like the following will work
in a query once you plug in the proper table and field names. However if they
have middle names, spaces around the comma, on missing commas, well.......

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.

 

[Guest]

You don't have to convert the data, you can use a select query to show the
right data with the split in the name, just as Jerry wrote.
If you still wont to convert your data, and insert the two names into to
seperate fields, you need to use update query, but first back up your data,
there is no undo.

UPDATE TableName SET TableName.FName=
Left([FieldName],Instr([FullName],",")-1) , TableName.LName =
Mid([FullName],Instr([FullName],",")+1)

--
\\// Live Long and Prosper \\//
BS"D

Thanks Ofer, but will this convert all the names in one stroke?

Assuming that there is a comma between the two values
Try this

For John
Left([FieldName],Instr([FieldName],",")-1)

For Smith
Mid([FieldName],Instr([FieldName],",")+1)

--
\\// Live Long and Prosper \\//
BS"D

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.

 

[Guest]

Thanks Jerry,

Pls show me where to input the following commands?

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;

Gratefully

Henry

Sure. However there could be problems if some, even one, record doesn't have
a comma in it. Also if someone swapped around the first and last names.
Middle initials and middle names also could present problems.

But if the data is clean and formatted as you say, 50K records or more
should work OK if not a little slow.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks Jerry,

Will this work on the whole "more than 10K" names?

If your names are like "John,Smith", something like the following will work
in a query once you plug in the proper table and field names. However if they
have middle names, spaces around the comma, on missing commas, well.......

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.

 

[Guest]

Hi Cai,

1. First change the SQL below with Names and tblNames to the correct table
and field name for your data.
2. Open the database (of course!).
3. Go to queries in the database window.
4. Click on New then OK with Design View selected.
5. Click Close without selecting any tables.
6. On the menu go to View, SQL View.
7. Past in the SQL.
8. Run.
9. If it doesn't work, find out why.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks Jerry,

Pls show me where to input the following commands?

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;

Gratefully

Henry

Sure. However there could be problems if some, even one, record doesn't have
a comma in it. Also if someone swapped around the first and last names.
Middle initials and middle names also could present problems.

But if the data is clean and formatted as you say, 50K records or more
should work OK if not a little slow.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks Jerry,

Will this work on the whole "more than 10K" names?

:

If your names are like "John,Smith", something like the following will work
in a query once you plug in the proper table and field names. However if they
have middle names, spaces around the comma, on missing commas, well.......

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.

 

[Guest]

Jerry,

I am trying hard but getting syntax errors.
Pls check below for me anything I did wrong.

The database is called Copy of NPY Services.

I typed:
SELECT Copy of NPY Services QuerylName.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM Copy of NPY Services QuerylNames;


Thanks
Henry

Thanks Jerry,

Pls show me where to input the following commands?

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;

Gratefully

Henry

Sure. However there could be problems if some, even one, record doesn't have
a comma in it. Also if someone swapped around the first and last names.
Middle initials and middle names also could present problems.

But if the data is clean and formatted as you say, 50K records or more
should work OK if not a little slow.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks Jerry,

Will this work on the whole "more than 10K" names?

:

If your names are like "John,Smith", something like the following will work
in a query once you plug in the proper table and field names. However if they
have middle names, spaces around the comma, on missing commas, well.......

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.

 

[Guest]

SELECT [Copy of NPY Services QuerylName].Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM [Copy of NPY Services QuerylName];

This is one of the biggest reasons to not put spaces in table, form, report,
and field names. You MUST remember to use square brackets [] if you have
spaces. I recommend underscores _ instead of spaces in all names.

Also this in the table name looks weird: QuerylName . Is that the name of
the table or query?

Speaking of names, "Names" is the name of the field in the table/query,
isn't it? Make sure.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,

I am trying hard but getting syntax errors.
Pls check below for me anything I did wrong.

The database is called Copy of NPY Services.

I typed:
SELECT Copy of NPY Services QuerylName.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM Copy of NPY Services QuerylNames;


Thanks
Henry

Thanks Jerry,

Pls show me where to input the following commands?

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;

Gratefully

Henry

Sure. However there could be problems if some, even one, record doesn't have
a comma in it. Also if someone swapped around the first and last names.
Middle initials and middle names also could present problems.

But if the data is clean and formatted as you say, 50K records or more
should work OK if not a little slow.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Thanks Jerry,

Will this work on the whole "more than 10K" names?

:

If your names are like "John,Smith", something like the following will work
in a query once you plug in the proper table and field names. However if they
have middle names, spaces around the comma, on missing commas, well.......

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.

 

[Guest]

Ok Jerry,
I went back to change the field "Pt Name" to "PtName" and type in the
following. I got message as syntax error "operator". Suggestions, pls.


SELECT[Copy of NPY Services|Name]. PtName,
Left([PtName],InStr(1,[PtNames],","-1) AS LastName,
Mid([PtName],InStr(1,[PtName],","+1) AS FirstName
FROM [Copy of NPY Services|Name];


Thanks
Henry

SELECT [Copy of NPY Services QuerylName].Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM [Copy of NPY Services QuerylName];

This is one of the biggest reasons to not put spaces in table, form, report,
and field names. You MUST remember to use square brackets [] if you have
spaces. I recommend underscores _ instead of spaces in all names.

Also this in the table name looks weird: QuerylName . Is that the name of
the table or query?

Speaking of names, "Names" is the name of the field in the table/query,
isn't it? Make sure.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,

I am trying hard but getting syntax errors.
Pls check below for me anything I did wrong.

The database is called Copy of NPY Services.

I typed:
SELECT Copy of NPY Services QuerylName.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM Copy of NPY Services QuerylNames;


Thanks
Henry

Thanks Jerry,

Pls show me where to input the following commands?

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;

Gratefully

Henry



:

Sure. However there could be problems if some, even one, record doesn't have
a comma in it. Also if someone swapped around the first and last names.
Middle initials and middle names also could present problems.

But if the data is clean and formatted as you say, 50K records or more
should work OK if not a little slow.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Thanks Jerry,

Will this work on the whole "more than 10K" names?

:

If your names are like "John,Smith", something like the following will work
in a query once you plug in the proper table and field names. However if they
have middle names, spaces around the comma, on missing commas, well.......

SELECT tblNames.Names,
Left([Names],InStr(1,[Names],",")-1) AS LastName,
Mid([Names],InStr(1,[Names],",")+1) AS FirstName
FROM tblNames;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I received a database that contains >10K names in the form of "John,Smith"
with no space. I need to convert this field into two as in "John" and
"Smith". Your help will be greatly appreciated.