Access 2000 Delete Field info after a comma

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
FrankSpokane said:
I would like to delete everything including the comma and everything after
it in the LastName field.
Click to expand...

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));
 
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));
 
FrankSpokane said:
The code I pasted into the criteria field is:

SET LastName = Mid(LastName, 1, (Instr(1, LastName, ",") - 1));
Click to expand...

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));
 
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.
Click to expand...

Update YourTable Set YourTable.[LastName] =
Left([LastName],InStr([LastName],",")-1);
 
FrankSpokane said:
Ok that worked like a charm - thanks much. I haven't used SQL before.
Click to expand...

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