Delete part of text from field

[prodeji]

Hi all

Here's my challenge:

I'm processing a customer info file with your standard demographics -
first name, last name, age, address etc.

The last name appears in all of the 'last name' fields; no problem.
However, in some of the 'first name' fields the last name appears
again, like so:

F_Name L_Name Title
Kris Doft Doft Manager
Johan Smith Accountant
Maria Katson Katson Director
Anne Charles Charles Supervisor

Is there a query that can get rid of the duplicate last name in the
'first name' fields?

Obviously it can't be a standardised 'delete last 4 characters in
F_Name field' type of deal as both the first and last names will have
differing lengths in any given record.

I think I basically have to tell the database to 'delete any character
that comes after a space ' ' in the F_Name field, but I don't know how
to do that...

Um... help?

Thanks

 

[John Spencer]

I would be careful doing this. Before running the update query make sure
you have a backup

UPDATE YourTable
SET F_Name = Left(F_Name, Instr(1, F_Name, " ")-1)
WHERE F_Name Like " *" & L_Name

In design view
-- Select your table
-- select F_name field
-- in the criteria ENTER Like " *" & [L_Name]
-- Select Query: Update from the menu
-- in update enter Left([F_Name], Instr(1, [F_Name], " ")-1)

IF you want to see the results BEFORE you actually update the records then
-- Select your table
-- select F_name field
-- in the criteria ENTER Like " *" & [L_Name]
-- in a new column enter
Left([F_Name], Instr(1, [F_Name], " ")-1)
as the field
-- Run the query.


John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

You can use an update query and a public function. You can call the function
from within the query. Here is how you would call the function from the
query in the query builder. Instead of just putting the F_NAME field in the
query, use a calculated field:

FirstName: FixName([F_NAME], [L_NAME])

Now the query

Public Function FixName(strFirstName As String, strLastName as String) As
String
If Instr(strFirstName, strLastName) = 0 Then
FixName = strFirstName
Else
FixName = Trim(Replace(strFirstName, strLastName ,vbNullstring))
End If
End Function

Put the function in a standard module.

 

[prodeji]

Guys,

It worked!!

Thanks so much

I would be careful doing this. Before running the update query make sure
you have a backup

UPDATE YourTable
SET F_Name = Left(F_Name, Instr(1, F_Name, " ")-1)
WHERE F_Name Like " *" & L_Name

In design view
-- Select your table
-- select F_name field
-- in the criteria ENTER Like " *" & [L_Name]
-- Select Query: Update from the menu
-- in update enter Left([F_Name], Instr(1, [F_Name], " ")-1)

IF you want to see the results BEFORE you actually update the records then
-- Select your table
-- select F_name field
-- in the criteria ENTER Like " *" & [L_Name]
-- in a new column enter
Left([F_Name], Instr(1, [F_Name], " ")-1)
as the field
-- Run the query.

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi all

Here's my challenge:

I'm processing a customer info file with your standard demographics -
first name, last name, age, address etc.

The last name appears in all of the 'last name' fields; no problem.
However, in some of the 'first name' fields the last name appears
again, like so:

F_Name L_Name Title
Kris Doft Doft Manager
Johan Smith Accountant
Maria Katson Katson Director
Anne Charles Charles Supervisor

Is there a query that can get rid of the duplicate last name in the
'first name' fields?

Obviously it can't be a standardised 'delete last 4 characters in
F_Name field' type of deal as both the first and last names will have
differing lengths in any given record.

I think I basically have to tell the database to 'delete any character
that comes after a space ' ' in the F_Name field, but I don't know how
to do that...

Um... help?

Thanks- Hide quoted text -

- Show quoted text -