Prev: Setting IE Cookies - Windows 7
Next: Scroll records with the mouse wheel in Access7
From: John Spencer on 24 Jun 2010 09:08
Ok, I tested this one and got out the bugs (I hope). It will replace only one
occurrence per field.

I am working on a more general procedure that will replace all or a specified
number of matches of any wild card string that is a defined length - you
cannot use * in the wild card string. I have made some progress, but I have
to figure out a way to determine the length of the wild card string.

Try this function and see if it will do what you want.
Public Function fReplace5Numbers(strIN)
Dim i As Long

If Len(strIN & "") = 0 Or Not strIN Like "[0-9][0-9][0-9][0-9][0-9]" Then
fReplace5Numbers = strIN
Else
For i = 1 To Len(strIN)

If Mid(strIN, i, 5) Like "[0-9][0-9][0-9][0-9][0-9]" Then
fReplace5Numbers = Left(strIN, i - 1) & "xxxxx" & Mid(strIN, i + 5)
Exit For
End If

Next i
End If

This function will not work on example number 4 since it has spaces in the
number. The function looks for an exact match of 5 number characters in a
row. So it will replace a 5-character postal code just as easily as it
replaces a 5-character invoice number. Or if the invoice is 6 numbers long it
will replace the first 5 characters.

bcap wrote:
> Hi John,
>
> I am sorry for the confusion and sincerely appreciate your responses
> (Salad�s too)!!!!
>
> Basically what is going on here is I have a Text field and we want to
> remove Invoice Numbers from the table and replace the invoice numbers
> with five X�s in place of the original number. The tricky part is
> that all the data was entered free hand and uncontrolled so there are
> different methods to how they are entered.
>
> The data entry has been fairly random. Some fields have just the
> invoice number, others have it surround my comments.
>
> For example:
>
> ID Comment
> 1 Invoice #55555 has been paid.
> 2 53412
> 3 87292 has been paid on 6/23/2010
> 4 Invoice Num: 55 322
>
> What I would like to be able to do is make the data look like this
> using the examples above:
>
> ID Comment
> 1 Invoice #XXXXX has been paid.
> 2 XXXXX
> 3 XXXXX has been paid on 6/23/2010
> 4 Invoice Num: XXXXX
>
> I hope this helps clarify the problem and once again thank you!!!!

--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
From: bcap on 24 Jun 2010 09:37
Thanks again John! It seems like this is only updating records to
xxxxx if the first set of characters are the consecutive numbers we
are looking for.
From: John Spencer on 24 Jun 2010 12:43
OH good gravy!!!! Try this corrected version.

I need either more coffee or less coffee. I forgot to add the any number of
characters wildcards in the initial test.

Public Function fReplace5Numbers(strIN)
Dim i As Long

If Len(strIN & "") = 0 Or Not strIN Like "*[0-9][0-9][0-9][0-9][0-9]*" Then
fReplace5Numbers = strIN
Else
For i = 1 To Len(strIN)

If Mid(strIN, i, 5) Like "[0-9][0-9][0-9][0-9][0-9]" Then
fReplace5Numbers = Left(strIN, i - 1) & "xxxxx" & Mid(strIN, i + 5)
Exit For
End If

Next i
End If

End Function

And now looking at it I could probably change the initial test to

IF Not strIn & "" LIKE "*[0-9][0-9][0-9][0-9][0-9]*" Then
fReplace5Numbers = strIN
ELSE
.....


bcap wrote:
> Thanks again John! It seems like this is only updating records to
> xxxxx if the first set of characters are the consecutive numbers we
> are looking for.

--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
From: bcap on 24 Jun 2010 15:40
THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
First  |  Prev  | 
Pages: 1 2 3
Prev: Setting IE Cookies - Windows 7
Next: Scroll records with the mouse wheel in Access7