Split Customer Name

[adam]

Hi,

I'm having a table named as "Customers" with four columns. The first one is named as "ID" which is the primary key and the second as "Customer Name", third as "First Name" & the fourth column as "Last Name".

In the Customer Name Column, I have more than 100 rows of (customer) names with a space between the First and the Last name. Columns "First Name" & "Last Name" are blank.

What I'm trying to get help is how to put the first name of customer name into "First Name" column & last name of the customer into the "Last Name" column from the "Customer Name" column.

Lets say for example the name "Jhon Mathew" as "Jhon" in "Fisrt Name" column & "Mathew" in the "Last Name" column.

Any help on this would be kindly appreciated.

Thanks in advance.

[HansV]

Activate the Create tab of the ribbon.
Click Query Design.
Select the Customers table.
Click Add, then click Close.
Access should now have activated the Design tab of the ribbon.
Click Update in the Query Type group.
Add the First Name field to the query grid.
Enter the following expression in the Update to row of this field:

Left([Customer Name],InStr([Customer Name]," ")-1)

Add the Last Name field to the query grid.
Enter the following expression in the Update to row of this field:

Mid([Customer Name],InStr([Customer Name]," ")+1)

Click the Run button in the Results group (of the Design tab of the ribbon).
If you want to be able to run the query again later on, save it.

[adam]

Thanks for the help Hans. It worked fine. In the same table I'm having a column named as "Address" with one more column as "Country".

The text in the column "Address" has two words separated by a "/".

The column "Country" is empty.

Lets say for example, the text in column "Address" is as Bluebay / Germany.

If this is the situation, how should I separate the word that comes after the slash to the column "Country" and keep the word before the slash in the same column "Address" with the slash being deleted?

[HansV]

Is there always a space before and after the slash, as in your example?

[adam]

Yes. There's is a slash before and after the slash.

[HansV]

The idea is the same as in the previous situation. Create an update query and add the Address and Country fields.
In the Update to row for Address, enter the expression

Left([Address],InStr([Address],"/")-2)

and in the Update to row for Country, enter

Mid([Address],InStr([Address],"/")+2)

The expressions use -2 and +2 instead of -1 and +1 to skip the space before/after the slash.

[adam]

Sorry I meant "space" before & after the slash. Will your suggestion help with the space?

[HansV]

Yes, I assumed that you meant "space before and after the slash" instead of "slash before and after the slash".