Extract 6 digit text string only?

J

JimiH

Registered User.
Local time
Today, 08:57
Joined
Feb 5, 2004
Messages
12
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
 
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?
 
Something like so:
Code: 
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
 
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
 
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
 
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
 

Attachments

i told you it was untested... :(

try this then:
Code: 
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
 
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
 
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. :rolleyes:
 
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.
Code: 
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
 
I used mid instead of the intended left...

I think this will do it....
Code: 
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
 
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
 
Hello all, just in case anyone else is trying this

The following code works

Code: 
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
 
Last edited by a moderator:
You must log in or register to reply here.

Similar threads

A
Filter Records
Replies
1
Views
363
June7
June7
MajP
Using A Collection Class to Raise Common Event
2
Replies
33
Views
837
Pat Hartman
P
P
Reading a long text file to insert rows in a table
Replies
8
Views
670
pacctono
P
B
Retrieve 6 numbers from string
Replies
2
Views
706
arnelgp
arnelgp
J
Colour text in Text Box
Replies
8
Views
709
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom