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:

> 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]*'
> 
Replace() is a function.  I suppose you could create a function in a 
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:
> 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]*'
> 

-- 

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

bcap wrote:
> 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!
> 

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:
> Hi John,  what would I change to be able to search for 5 consecutive
> numbers anywhere within the string?  Thank you again!

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:
> Hi John,  what would I change to be able to search for 5 consecutive
> numbers anywhere within the string?  Thank you again!

-- 

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=92s 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=92s 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!!!!

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

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.

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

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