Remove middle name

[Robert Gillard]

I have a field [sender name] which contains a persons surname, 1st name and
work area.

Brown , Pete : floor1
Smit , Jim : floor34

I an trying to use an update query to remove the 1st name to a new field. I
have tried the following but I get an answer of "type conversion failour"



Mid([sender name],InStr([sender name],",")+2,InStr([sender
name],":")-InStr([sender name],",")-2)


Bob

 

[Al Camp]

Robert,
I tried this and it worked... as long as ALL SenderName entries look like
your example. Specifically, that the comma is always a space beyond the
LastName.
=Left([SenderName],InStr([SenderName],",")-1)
yields..
Brown
Smit

I'd do the update query first, and then...
=Mid([SenderName],InStr([SenderName],",")+1)
could be used to remove the Brown,(space) and Smit,(space) from
the SenderName field.

 

[Guest]

You get a type conversion failure when the two related
fields are not defined the same (ie. number VS text, text
VS memo, etc.) Also, I'm not sure your code is right. I
did this many years ago and got it to work perfectly after
some trial and error. To long ago to remember tho.
Sorry. Keep at it, you'll get it.

 

[Fredg]

Robert,
You haven't indicated which version of Access.
This should work in all versions.

Assuming all the records have the same layout of:
Brown , Pete : floor1
this works OK for me to insert the first name into a new field.
It does not 'remove' the name from the existing field.

Update YourTable Set YourTable.FirstNameField =
Mid([Sender name],InStr([Sender name],",")+2,(InStr([Sender
name],":")-2)-(InStr([Sender Name],","))-1);

To actually alter the existing [Sender name] field from
Brown , Pete : floor 1
to
Brown : floor 1
and separate the first name to a new field, use:

UPDATE YourTable SET YourTable.[FirstNameField] = Mid([Sender
name],InStr([Sender name],",")+2,(InStr([Sender name],":")-2)-(InStr([Sender
name],","))-1), YourTable.[Sender name] = Left([Sender name],InStr([Sender
name],",")-2) & Mid([Sender name],InStr([Sender name],":")-1)
WHERE (((YourTable.[Sender name]) Is Not Null));