And another update qurey problem

[Maggic]

I thought I had a solution for this from an answer to another post .. but
sad to say I was wrong ..

I have imported some data and find that the LASTNAME field contains both
first and last names separated by a comma as in "LastName,FirstName". I
need to take the FirstName portion of the LastName field and place it in the
FirstName field and then eliminate the first name text from the LastName
field.

Here is the SQL used to try to separate first and last names.

UPDATE Contacts SET Contacts.FirstName =
Right([LastName],InStr([LastName],","))
WHERE (((Contacts.LastName) Like [LastName]="*,*"));

I get 0 records updated with this ...

I tried just using the UPDATE line and get the message that all records will
be updated (which should not be the case, as some data will not need
converting), and then get the error that there is a "type conversion"
failure .. note both fields are defined as "text".

Some help with this and also with how to delete the first name text would be
much appreciated as usual.

As an aside .. Can anyone suggest a reference manual that covers the data
manipulation functions of SQL. I have taken out "SQL in 21 days" from the
library, but find it doesn't cover data manipulation to the extent that I
appear to need.

Thanks in advance, & please excuse my ignorance of all things SQL ...
Maggic

 

[Dan Artuso]

Hi,
Try:
WHERE (((Contacts.LastName) Like "*,*"));

 

[Fredg]

You write that your LastName field can look like this,
Smith,James (no space after the comma),
and later you said that some records do not need converting,
so that means some are just
Smith.

Make sure you first back up your table!
If some of the name have, and some do not have the combined First and Last
Names,
try it like this:

UPDATE Contacts SET Contacts.FirstName =
Mid([LastName],InStr([LastName],",")+1)
WHERE InStr(LastName,",") >0

Note: if there is a space after the comma, use +2 instead of the +1 above.

That takes care of the FirstName field, but then you still have the LastName
field with both names in it.
You can change both fields in one query.

UPDATE Contacts SET Contacts.FirstName =
Mid([LastName],InStr([LastName],",")+1), Contacts.LastName =
Left([LastName],InStr([LastName],",")-1)
WHERE InStr([LastName],",")>"0";

Hope this helps.

 

[Steve Schapel]

Maggic,

The syntax of your WHERE clause is incorrect. Also, your use of the
Right function will produce incorrect results. For example, if your
LastName field is Smith,Fred then the value of InStr([LastName],",")
will be 6, therefore the output of
Right([LastName],InStr([LastName],",")) will be the rightmost 6
characters, i.e. "h,Fred".

Try this...
UPDATE Contacts
SET Contacts.FirstName = Mid([LastName],InStr([LastName],",")+1)
WHERE ((Contacts.LastName) Like "*,*");

- Steve Schapel, Microsoft Access MVP

 

[Maggic]

Many thanks to all who replied ... I used Fred's code to update both fields
at the same time and of course it worked perfectly. Great .. I now have
"clean" converted data ... thanks again.


Maggic

You write that your LastName field can look like this,
Smith,James (no space after the comma),
and later you said that some records do not need converting,
so that means some are just
Smith.

Make sure you first back up your table!
If some of the name have, and some do not have the combined First and Last
Names,
try it like this:

UPDATE Contacts SET Contacts.FirstName =
Mid([LastName],InStr([LastName],",")+1)
WHERE InStr(LastName,",") >0

Note: if there is a space after the comma, use +2 instead of the +1 above.

That takes care of the FirstName field, but then you still have the LastName
field with both names in it.
You can change both fields in one query.

UPDATE Contacts SET Contacts.FirstName =
Mid([LastName],InStr([LastName],",")+1), Contacts.LastName =
Left([LastName],InStr([LastName],",")-1)
WHERE InStr([LastName],",")>"0";

Hope this helps.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

I thought I had a solution for this from an answer to another post .. but
sad to say I was wrong ..

I have imported some data and find that the LASTNAME field contains both
first and last names separated by a comma as in "LastName,FirstName". I
need to take the FirstName portion of the LastName field and place it in the
FirstName field and then eliminate the first name text from the LastName
field.

Here is the SQL used to try to separate first and last names.

UPDATE Contacts SET Contacts.FirstName =
Right([LastName],InStr([LastName],","))
WHERE (((Contacts.LastName) Like [LastName]="*,*"));

I get 0 records updated with this ...

I tried just using the UPDATE line and get the message that all records will
be updated (which should not be the case, as some data will not need
converting), and then get the error that there is a "type conversion"
failure .. note both fields are defined as "text".

Some help with this and also with how to delete the first name text

would

be
much appreciated as usual.

As an aside .. Can anyone suggest a reference manual that covers the data
manipulation functions of SQL. I have taken out "SQL in 21 days" from the
library, but find it doesn't cover data manipulation to the extent that I
appear to need.

Thanks in advance, & please excuse my ignorance of all things SQL ...
Maggic