Prev: Setting IE Cookies - Windows 7
Next: Scroll records with the mouse wheel in Access7
From: bcap on 22 Jun 2010 09:58
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]*'

From: Salad on 22 Jun 2010 10:17
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.
From: bcap on 22 Jun 2010 11:06
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!

From: John Spencer on 22 Jun 2010 12:50
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
From: Salad on 22 Jun 2010 13:09
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
 |  Next  |  Last
Pages: 1 2 3
Prev: Setting IE Cookies - Windows 7
Next: Scroll records with the mouse wheel in Access7