Prev: Setting IE Cookies - Windows 7
Next: Scroll records with the mouse wheel in Access7
From: bcap on 22 Jun 2010 16:33
Thank you John and Salad for your help, very much
appreciated!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! =)
From: bcap on 23 Jun 2010 09:34
Hi John, what would I change to be able to search for 5 consecutive
numbers anywhere within the string? Thank you again!
From: Salad on 23 Jun 2010 10:27
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



From: John Spencer on 23 Jun 2010 15:56
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
From: bcap on 23 Jun 2010 17:03
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!!!!
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Setting IE Cookies - Windows 7
Next: Scroll records with the mouse wheel in Access7