Extract 6 digit text string only?

[JimiH]

Hello,

I have some code that changes the type of text string below

GV123456, 12345, H123456, 12, 12

to

123456 12345 123456 12 12

ie, removed letters, punctuation etc

Now the problem is, I only want to keep the 6 digit string & delete the rest, so out of the second string I will keep

123456 & 123456

Is this possible, I'm sure it is!!

Code

Private Sub Command6_Click()
Dim strOut As String
Dim i As Integer
Dim sLen As Integer
Dim StrPoint As Integer

i = Len(Me.Text2)
Me.Text9 = i
StrPoint = 1
For sLen = 1 To i

If IsNumeric(Mid(Me.Text2, sLen, 1)) Then
Me.Text7 = Me.Text7 & Mid(Me.Text2, sLen, 1)
Else: Me.Text7 = Me.Text7 & " "


End If
Next sLen
End Sub

Thanks

Geoff

 

[dcx693]

Are your text strings always of this form:
GV123456, 12345, H123456, 12, 12
where you want to kee the numbers in the x positions?
GVxxxxxx, 12345, Hxxxxxx, 12, 12
Or can they vary?

 

[namliam]

Something like so:

Private Sub Command6_Click()
Dim strOut As String
Dim i As Integer
Dim sLen As Integer
Dim StrPoint As Integer
Dim Numbercount As Integer
i = Len(Me.Text2)
Me.Text9 = i
StrPoint = 1

For sLen = 1 To i
    If IsNumeric(Mid(Me.Text2, sLen, 1)) Then
        Me.text7 = Me.text7 & Mid(Me.Text2, sLen, 1)
        Numbercount = Numbercount + 1
    Else
        If Numbercount = 6 Then
            Me.text7 = Me.text7 & " " 'keep it
        Else
            'found numbers are useless, throw them away
            Me.text7 = Mid(Me.text7, Len(Me.text7) - Numbercount)
            Numbercount = 0
        End If
    End If
Next sLen
End Sub

And tell me your NOT actuall using textboxes like Text7 but your using actual names or that this is a test.... or something....

Above code is untested, but should/might work... try it...

Regards

 

[JimiH]

Thanks for the replies, I will try them out

To answer your questions

1, The text strings do change

Ex

1st record = 125541, 142556, obsolete

2nd record =21561

3rd record = 145698, revised construction 14569

ect

I think namliam's code caters for this, also this is a one off conversion so to keep it simple I used the me.text7 method
instead of naming the text boxes


Thanks

Geoff

 

[namliam]

OK no probs, i hope it works, I trew it together in 10 secs or so....

Try it out.... and let me know, would be fun if it works.....

Regards

 

[JimiH]

Hi namliam, didn't work

invalid procedure call in this line

Me.text7 = Mid(Me.text7, Len(Me.text7) - Numbercount)

Hope you can help

Thanks

Geoff

ps

I've attached the db

 

[namliam]

i told you it was untested...

try this then:

Dim strOut As String
Dim i As Integer
Dim sLen As Integer
Dim StrPoint As Integer
Dim Numbercount As Integer
i = Len(Me.Text2)
Me.Text9 = i
StrPoint = 1

For sLen = 1 To i
    If IsNumeric(Mid(Me.Text2, sLen, 1)) Then
        Me.text7 = Me.text7 & Mid(Me.Text2, sLen, 1)
        Numbercount = Numbercount + 1
    Else
        If Numbercount = 6 Then
            Me.text7 = Me.text7 & " " 'keep it
        Else
            'found numbers are useless, throw them away
            Me.text7 = Mid(Me.text7, Len(Me.text7) - Numbercount + 1)
            Numbercount = 0
        End If
    End If
Next sLen

 

[JimiH]

Hey, thanks for your time namliam


Still cant get it to work

If I type in 12345,123456,123456, 123456

I get 123456, instead of 123456 123456 123456

If I type in 12345,123456,123456

I get 123456 123456, which is correct?

When I type 123456,12345

I get 123456 12345?

Something isn't working right but I dont know what

Thanks

Geoff

 

[Mile-O]

What version of Access are you using? If it's A2000 or above you can do this *

• Use the Split() function on the field to put the values into an array;
• Loop through each value in the array looking only for those with a length of 6 characters;
• Rebuild a string with only those array values of six characetrs in length

* I don't have time to write the code as I'm off to the pub.

 

[JimiH]

Thanks Mile, I'll look that up.

Geoff

 

[raskew]

Geoff -

Here's a tested example along the lines of Mile-O's suggestion. It's written in '97 so it includes Function ySplit(), which simulates the Split() function in '2000 and later.

Function NDigits(pStr As String, pLen As Integer, pDelim As String) As String
'*******************************************
'Source:    [url]http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=61462[/url]
'Purpose:   Return integers >= pLen, eliminating
'           non-numeric characters in the process
'Coded by:  raskew
'Date:      February 08, 2004
'Calls:     Function ySplit()
'To test from debug window:
'Inputs:    1) ? nDigits("145698, revised construction 14569", 5, ",")
'           2) ? nDigits("GV123456, 12345, H123456, 12, 12", 6, ",")
'Output:    1) 145698 14569
'           2) 123456 123456
'*******************************************

Dim aStr()  As String
Dim strHold As String
Dim strKeep As String
Dim strTry  As String
Dim i       As Integer
Dim n       As Integer

    strHold = Trim(pStr) & IIf(Right(Trim(pStr), 1) <> pDelim, pDelim, "")
    i = 0
    ReDim aStr(0 To 0)
    Do While InStr(strHold, pDelim) > 0
       strTry = ySplit(strHold, pDelim, True)
       strHold = LTrim(ySplit(strHold, pDelim, False))
       If Len(strTry) >= pLen Then      'don't process strings if len < pLen
          For n = 1 To Len(strTry)
             If IsNumeric(Mid(strTry, n, 1)) Then
                If Len(Mid(strTry, n, 1)) >= pDelim Then
                   aStr(i) = Mid(strTry, n)
                   i = i + 1
                   ReDim Preserve aStr(i)
                   Exit For
                Else
                   Exit For
                End If
             End If
          Next n
       End If
    Loop
    i = i - 1
    ReDim Preserve aStr(i)
    For n = 0 To i
       strKeep = strKeep & " " & aStr(n)
    Next n
    NDigits = strKeep
End Function
'*******************************************
Function ySplit(ByVal pTarget As String, pItem As String, Optional ShowLeft As Boolean = True) As String
Dim strLeft As String, strRight As String
Dim n As Integer

n = InStr(pTarget, pItem)
If n = 0 Then
   ySplit = ""
Else
   ShowLeft = IIf(IsMissing(ShowLeft), True, ShowLeft)
   strLeft = Left(pTarget, n - 1)
   strRight = Mid(pTarget, n + 1)
   ySplit = Trim(IIf(ShowLeft, strLeft, strRight))
End If
End Function
'*******************************************

HTH - Bob

 

[namliam]

I used mid instead of the intended left...

I think this will do it....

Dim strOut As String
Dim i As Integer
Dim sLen As Integer
Dim StrPoint As Integer
Dim Numbercount As Integer
i = Len(Me.Text2)
Me.Text9 = i
StrPoint = 1
Me.text7 = ""
For sLen = 1 To i
    If IsNumeric(Mid(Me.Text2, sLen, 1)) Then
        Me.text7 = Me.text7 & Mid(Me.Text2, sLen, 1)
        Numbercount = Numbercount + 1
    Else
        Numbercount = 0
        If Numbercount = 6 Then
            Me.text7 = Me.text7 & " " 'keep it
        ElseIf Numbercount > 0 and len(me.text7 > numbercount)Then
            'found numbers are useless, throw them away
            Me.text7 = Left(Me.text7, Len(Me.text7) - Numbercount)
        ElseIf Numbercount > 0 and len(me.text7 = numbercount)Then
            Me.text7 = ""
        Else 'no new numbers yet
            ' do nothing
        End If
    End If
Next sLen

Regards

 

[JimiH]

Thanks for your help

Raskew

MsgBox NDigits("GV123456,TPL12345", 6, ",")

Results in "123456 12345"

MsgBox NDigits("GV123456,released,456789", 6, ",")

Results in "123456 456789"

Any thoughts? it seems to include a 5 digit string if letters proceed it.

Thanks

Geoff

 

[JimiH]

Hello all, just in case anyone else is trying this

The following code works

Private Sub Command6_Click()
i = Len(Form_Form1!Text2) + 1

Numbercount = 0

Form_Form1!Text9 = i
StrPoint = 1
strOut = ""
Form_Form1!Text7 = ""

For sLen = 1 To i
    If IsNumeric(Mid(Form_Form1!Text2, sLen, 1)) Then
        strOut = strOut & Mid(Form_Form1!Text2, sLen, 1)
        Numbercount = Numbercount + 1
    End If
    
    If Not IsNumeric(Mid(Form_Form1!Text2, sLen, 1)) Then
        If Numbercount = 6 Then
            Form_Form1!Text7 = Form_Form1!Text7 & strOut & ","
            strOut = ""
            Numbercount = 0
            
        Else
             Form_Form1!Text7 = Trim(Form_Form1!Text7) & "" 'bin it
             Numbercount = 0
             strOut = ""
             
        End If
        
    End If
Next sLen
End Sub

Thanks

Geoff