Writing a macro in access to remove specific characters

[Tamara]

Hello everybody,

I have been looking in the previous posts for a similar macro, but I haven't
been able to find it.

I use the Office 2003 and I need to write a macro (or a Visual Basic
routine) that loops trough the Database (or predefined columns) and remove
specific characters in the column/Database.

I have a very large Database, and some records have double spaces in them
(char 20 20) or a space at the beginning of the record.

As I need to remove also some other characters, it would be handy if the
character to delete is variable.

The "search and replace" function in the menu cannot be used to do this.
I have tried to used the "search and replace" function and the result was a
complete disaster (luckely I made a backup first).

Any help is very appreciated.

TIA,

Tamara

 

[Al Camp]

Use an Update query to find these particular records, correct the data, and
place that value back into the field.
Using String functions (Left, Right, Mid, and InStr) you can accomplish this
the Update query's "find & correct" logic.

Each of your examples involves a different "find & correct" method, so one
Update query will not work.
You'll need to run a seperate (or revised) query for each instance.

For those fields that have a space " " in position1... Left(YourFieldName,1)
= " " would find them, and Mid(YourFieldName,2) would be used as the
replacement value.

For " " (double spaces) within the string...
1. Use InStr criteria to determine the " " position in the original string.
(say ex. InStr=7)
2. Capture portion of the string Left (ex. 7-1) of the " ".
3. Capture the portion of the string right of the " " (ex. Mid 7+2)
4. Concatenate those 2 strings together as the "update" value.

This code is not tested, but should work... please tweak my code if
necessary.
Criteria to find: InStr(YourFieldName," ") > 0
Replacement value: Left(YourFieldName, Instr(YourFieldName)-1) &
Mid(YourFieldName, InStr(YourFieldName) +2)

This will work whether the " " is in position 3 or 7 or whatever...

The real solution is to prevent these entries in the first place, rather
than continuously have to hunt them down after the fact.

AND....
1. Backup
2. Backup
3. Backup

hth
Al Camp

 

[Al Camp]

OK Tamara. I meant for my Correction post to go under your thread, but it
came up as a seperate Re:
Glad you found it!
Al Camp