Editing data in a table - any 'batch' way of doing so?

[rexnervous]

I have several thousand rows of (text) data that have some incorrect pieces. I need a way to delete part of the data but leave the rest intact.

Example:

Current data: "Bloomfield, CT"

Needs to become: Bloomfield

In other words, I need to remove the left quote, and everything after (including) the comma.

I would think this would be some kind of (relatively) simple query, but I can't figure it out.

Help?

 

[rexnervous]

I should add that I know how to pull the particular rows I want out ([Criteria = Like '*,*'] for instance), but I'm not sure how to tell Access to delete the comma and everything after, and then update the rows with the new, fixed data.

 

[Legacy 16232]

In the example, I would use the Edit -> Replace Command on the column like:
Replace - ,*
With - nothing

If you want the right quote removed as well, then use
Replace - "
With - nothing

All of your data may not fit that neatly, but you can be creative with the Replace utility, especially when you use a wildcard with it.

Another option to update a lot of Access data is an Update Query, but Text doesn't usually work well in an Update.

 

[rexnervous]

Hey, thanks. That worked great. I was looking for something a little more...elegant, I think - trying to teach myself SQL using Access somewhat, so was thinking along the lines of a query - but your solution was simple and worked

 

[mdmilner]

Yes, Access can easily accomplish what you ask, but if this was a one time conversion, why not use the Replace feature?

What you're looking for is this: (this is the LONG explanation)

UPDATE tblName SET fldOne=how_to_manipulate WHERE (how to id the proper entries)

What 'how_to_manipulate' looks like depends on what exactly you need to do, but it would be a combination of use of the Len/InStr/Left/Right/Mid functions.

Len([fieldname]) returns length of field contents
InStr([fieldname],[charstring_to_match]) returns position of first character of possibly a multi character string

Left([fieldname],x) returns x characters starting from left
Right (does same from right)
Mid([fieldname],x,y) returns y characters starting at position x

So...assuming that ALL fields have the same pattern - all have quotes and all have a comma.

Mid([fld],2,InStr([fld],',')-2)
--
Final answer:

UPDATE tblName SET [fld]=Mid([fld],2,InStr([fld],',')-2)  WHERE [fld] LIKE '"*, *"'

fld=field to read and change the value on
tblName=name of table
WHERE=that's up to you

Yes, you can get much more complex than this. Yes you can remove the [] brackets if your fieldname does not include spaces (but brackets always work)

Mike