Split CustomerName into First and Last

[ryguy7272]

I tried to follow a couple of examples on the web, but I'm just not getting
it. I have one Table with the following Fields: CustomerName, First, and
Last. CustomerName has names like this: Gates, Bill. I am trying to split
the names, but the Update Query keeps failing (message says 'Cannot update
'First'; field not updateable'). My SQL is below:
UPDATE Table1 SET [First] =
Left([CustomerName],InStr(1,[CustomerName],",")-1), [Last] =
Right(Trim([CustomerName]),Len(Trim([CustomerName]))-InStr(1,[CustomerName],","));


I don't think it is a problem with the SQL; I think it is a problem with the
Table, and the three fields that I am using. Can someone please walk me
through it?

Thanks so much!!
Ryan---

 

[Bob Barrows [MVP]]

I tried to follow a couple of examples on the web, but I'm just not
getting it. I have one Table with the following Fields:
CustomerName, First, and Last. CustomerName has names like this:
Gates, Bill. I am trying to split the names, but the Update Query
keeps failing (message says 'Cannot update 'First'; field not
updateable'). My SQL is below:
UPDATE Table1 SET [First] =
Left([CustomerName],InStr(1,[CustomerName],",")-1), [Last] =
Right(Trim([CustomerName]),Len(Trim([CustomerName]))-InStr(1,[CustomerNa
me],","));


I don't think it is a problem with the SQL; I think it is a problem
with the Table, and the three fields that I am using. Can someone
please walk me through it?

Hopefully this is a one-time task that will allow you to delete that
CustomerName field, and not something you plan to do periodically. If
so, you should give those fields better names: for wone thing, those are
the names of builtin Jet functions, something which you should strive to
avoid. How about CustFirstName and CustLastName? Nice and descriptive,
right?

Now, on to the query, with which I can find nothing wrong. Try breaking
it up:

UPDATE Table1 SET [CustFirstName] =
Left([CustomerName],InStr(1,[CustomerName],",")-1)

does that fail?

I think the use of Mid would simplify the other expression:
UPDATE Table1 SET [CustLastName] =
LTrim(Mid([CustomerName],InStr(1,[CustomerName],",")+1))

 

[ryguy7272]

I got it! Thank you very much Bob!
Regards,
Ryan---

--
RyGuy

I tried to follow a couple of examples on the web, but I'm just not
getting it. I have one Table with the following Fields:
CustomerName, First, and Last. CustomerName has names like this:
Gates, Bill. I am trying to split the names, but the Update Query
keeps failing (message says 'Cannot update 'First'; field not
updateable'). My SQL is below:
UPDATE Table1 SET [First] =
Left([CustomerName],InStr(1,[CustomerName],",")-1), [Last] =
Right(Trim([CustomerName]),Len(Trim([CustomerName]))-InStr(1,[CustomerNa
me],","));


I don't think it is a problem with the SQL; I think it is a problem
with the Table, and the three fields that I am using. Can someone
please walk me through it?

Hopefully this is a one-time task that will allow you to delete that
CustomerName field, and not something you plan to do periodically. If
so, you should give those fields better names: for wone thing, those are
the names of builtin Jet functions, something which you should strive to
avoid. How about CustFirstName and CustLastName? Nice and descriptive,
right?

Now, on to the query, with which I can find nothing wrong. Try breaking
it up:

UPDATE Table1 SET [CustFirstName] =
Left([CustomerName],InStr(1,[CustomerName],",")-1)

does that fail?

I think the use of Mid would simplify the other expression:
UPDATE Table1 SET [CustLastName] =
LTrim(Mid([CustomerName],InStr(1,[CustomerName],",")+1))

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.