removing characters from field in MS Access database table

Question

Using MS Access 2010. I have a field in a table that contains windows path names surrounded by quotes, like this ``` "C:\My Documents\Photos\img1.jpg" "C:\My Documents\Photos\products\gizmo.jpg" "C:\My Documents\Photos\img5.jpg" ``` and so on. I need to get rid of the quotes so the column looks like this: ``` C:\My Documents\Photos\img1.jpg C:\My Documents\Photos\products\gizmo.jpg C:\My Documents\Photos\img5.jpg ``` Is there a way to write an update query to do this? OR a better way to do it altogether?

Accepted Answer

If you will be doing this from within an Access session, using Access 2000 or later, you can use the `Replace()` function in an update query to remove the quotes. Remove would mean replace them with an empty string.

```
UPDATE YourTable
SET path_field = Replace(path_field, '"', '');
```

If any of those path strings could include quotes within them (yuck!), consider the `Mid()` function ... ask it to start at the 2nd character (skipping the lead quote), and return the number of characters equivalent to `Len(path_field) - 2`

```
UPDATE YourTable
SET path_field = Mid(path_field, 2, Len(path_field) - 2);
```

Either way, you may want to include a WHERE clause to ignore rows without `path_field` values.

```
WHERE Len(path_field) > 0
```

And if you must do this again when new data is added, use a different WHERE clause to ensure you UPDATE only those rows whose `path_field` values start and end with quotes.

```
WHERE path_field Like '"*"'
```

That was using the * wild card for Access' default ANSI 89 mode. If you will do this from ADO (ANSI 92 mode), use the % wild card.

```
WHERE path_field Like '"%"'
```

... or use `ALike` and the % wild card with either mode.

```
WHERE path_field ALike '"%"'
```

removing characters from field in MS Access database table

Solution

Problem