Hi,

I am trying to create an update statement that will replace values
based on a wild card and only replace the wild card data (not the
entire field). Does anyone have any suggestions? Is this even
possible?

Here is my code:

UPDATE

UPDATE MyTable
SET MyField = replace(MyField, '*[0-9][0-9][0-9][0-9][0-9]*' ,
'XXXXX')
WHERE (MyTable.MyField) Like '*[0-9][0-9][0-9][0-9][0-9]*'

bcap wrote:

Module called WildcardReplace(OldValue, NewValue) and use Split() or
Instr() in the function to parse out and create the new string.

Hi,

Thank you very much for the response. So are you suggeting that I
remove all characters that are not numeric from my field using a SPLIT
Function? Do you happen to have an example of this?

Thank you again!

Replace does not work with wildcards, neither does Instr

You need to write a custom VBA function to do this or use something more
powerful in terms of string manipulation - i.e., REGEX (Regular Expressions)

For the specific case you might use something like the following to replace
one incidence of five numbers in a row. With a little work this could be
generalized. As written you would have to only pass in values that have a
string of 5 digits - otherwise you will get null returned.

Public Function fReplace5Numbers(strIN)
Dim i as Long

For i = 1 to Len(strIn)
IF Mid(strIn,1,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 Function

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE MyTable
SET MyField = fReplace5Numbers(MyField)
WHERE (MyTable.MyField) Like '*[0-9][0-9][0-9][0-9][0-9]*'



bcap wrote:
--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

bcap wrote:
I thought you were replacing a string, not a range. Maybe this will
provide a direction.

Sub Junk()
Dim MyField As String
Dim MyNewField As String
MyField = "The current value is " & _
"123ABC [0-9][0-9][0-9][0-9][0-9] and I want " & _
"[0-9][0-9][0-9][0-9][0-9] to be XXXXX How Do I Do That"
MyNewField = ConvertJunk(MyField, "09", "XXXXX")
End Sub
Function ConvertJunk(strOld As String, strRange As String, _
strNewVal As String) As String

Dim intFor As Integer
Dim strNew As String
ConvertJunk = strOld

'I assumed number, you could use ASC for chars.
For intFor = Left(strRange, 1) To Right(strRange, 1)
ConvertJunk = Replace(ConvertJunk, intFor, strNewVal)
Next
MsgBox "it was " & strOld & vbNewLine & vbNewLine & _
" and now " & ConvertJunk
End Function

Thank you John and Salad for your help, very much
appreciated!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! =)

Hi John, what would I change to be able to search for 5 consecutive
numbers anywhere within the string? Thank you again!

bcap wrote:
Sub String5()

Dim intFor As Integer
Dim strNum As String
Dim strToSearch As String

'display number and next 4
strNum = "1"
For intFor = CInt(strNum) To CInt(strNum) + 4
MsgBox intFor
Next

'display numbers in string
strNum = "13579"
For intFor = 1 To Len(strNum)
MsgBox Mid(strNum, intFor, 1)
Next

'display only 1 and 9
strToSearch = "19"
For intFor = 1 To Len(strNum)
If InStr(strToSearch, Mid(strNum, intFor, 1)) Then
MsgBox Mid(strNum, intFor, 1)
End If
Next

End Sub

Sorry, I don't understand the question.

Do you want to replace every instance of 5 consecutive numbers in a string?
That is if the string were
aaas 12345 zzzzz67890 jj
then you want
aaas zzzzz jj
returned?

You could call the current function repeatedly to do so.

I will take a look at this and try to get back to you tomorrow. By the way, I
noticed a typo in the posting. I dropped an ending bracket in the like clause


Public Function fReplace5Numbers(strIN)
Dim i as Long

For i = 1 to Len(strIn)
IF Mid(strIn,1,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 Function



bcap wrote:
--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

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!!!!