Update Statement - Replace, Wildcard, and partial update?

#11

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:

Quote:

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

#12

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.

#13

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:

Quote:

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

#14

THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Update Statement - Replace, Wildcard, and partial update?

comp.databases.ms-access comp.databases.ms-access

Re: Update Statement - Replace, Wildcard, and partial update? - 06-24-2010 , 08:08 AM

Re: Update Statement - Replace, Wildcard, and partial update? - 06-24-2010 , 08:37 AM

Re: Update Statement - Replace, Wildcard, and partial update? - 06-24-2010 , 11:43 AM

Re: Update Statement - Replace, Wildcard, and partial update? - 06-24-2010 , 02:40 PM