Access 2000 Delete Field info after a comma

[Guest]

I have a database that has over 4000 records.

One field named LastName is a text field, and was supposed to have a
Lastname entered into it, as there is also a FirstName field.

However, the data entry people entered over half the names into the LastName
field as LastName,Firstname or LastName, FirstName and then also entered the
Firstname correctly into the Firstname field.

I would like to delete everything including the comma and everything after
it in the LastName field.

Would anyone have a routine/way to do this? Thanks.

 

[Granny Spitz via AccessMonster.com]

I would like to delete everything including the comma and everything after
it in the LastName field.

First make a backup of your table in case something goes wrong, then try this
query:

UPDATE TableName
SET LastName = Mid(LastName, 1, (Instr(1, LastName, ",") - 1));

 

[Guest]

I created an Update query and cut and pasted your code into the criteria
field, but got this error message:

The expression you entered contains invalid syntax
You omitted an operand or operator,you entered an invalid character or
comma, or you entered text without surround it in quotation marks.

The code I pasted into the criteria field is:

SET LastName = Mid(LastName, 1, (Instr(1, LastName, ",") - 1));

 

[Granny Spitz via AccessMonster.com]

The code I pasted into the criteria field is:

SET LastName = Mid(LastName, 1, (Instr(1, LastName, ",") - 1));

Don't use the QBE. Switch to SQL view (View | SQL View ) and paste the whole
expression into the white space to overwrite whatever text shows. Change
"TableName" to whatever yours is and run the query.

UPDATE TableName
SET LastName = Mid(LastName, 1, (Instr(1, LastName, ",") - 1));

 

[fredg]

I have a database that has over 4000 records.

One field named LastName is a text field, and was supposed to have a
Lastname entered into it, as there is also a FirstName field.

However, the data entry people entered over half the names into the LastName
field as LastName,Firstname or LastName, FirstName and then also entered the
Firstname correctly into the Firstname field.

I would like to delete everything including the comma and everything after
it in the LastName field.

Would anyone have a routine/way to do this? Thanks.

Update YourTable Set YourTable.[LastName] =
Left([LastName],InStr([LastName],",")-1);

 

[Guest]

Ok that worked like a charm - thanks much. I haven't used SQL before.

 

[Granny Spitz via AccessMonster.com]

Ok that worked like a charm - thanks much. I haven't used SQL before.

You're welcome. Update queries are sometimes easier to just use the SQL view
instead of the QBE. This is one of them.