Remove middle name

  • Thread starter Thread starter Robert Gillard
  • Start date Start date
R

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
 
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.
 
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.
 
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));
 
Back
Top