Re: Writing a macro in access to remove specific characters
- From: "Al Camp" <anon@xxxxxxxx>
- Date: Thu, 4 Aug 2005 15:04:51 -0400
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
"Al Camp" <anon@xxxxxxxx> wrote in message
news:ONJCZTPmFHA.2852@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
> "Tamara" <what's_in_a_name@xxxxxxxxxxx> wrote in message
> news:3le2siF11ppomU1@xxxxxxxxxxxxxxxxx
>> 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
>>
>>
>
>
.
- References:
- Prev by Date: Re: CORRECTION
- Next by Date: Re: Using Macro in Forms
- Previous by thread: Re: Writing a macro in access to remove specific characters
- Next by thread: Re: CORRECTION
- Index(es):
Relevant Pages
|
