Separating ID numbers from a comment field 1

[JonP46]

I have a rather large table that has ID numbers in a comment field. Some comment fields can have up to 20 ID numbers. Of course, I wnat to feather the ID numbers from the comment field creating a separate row for each ID. Fortunately, the ID's are mostly integers -- so I think that I could loop through a set of integers and create a new record if a string in the comment field compared with the current integer in the loop. I would probably do something like:


While (not last record)
for (i = 1; i < 3000; i++)
if (comment like 'i')
make new record with i as the id
endif
endfor
endwhile

I just not really sure where to start. Thanks.

 

[MichaelRed]

There's trouble right here in Ms. Access City. Your syntax is for C++, Ms. Access's native 'code' language is VBA. Never the twain shall meet?

Beyond that, there are a few details to be understood before I can offer much in the way of help.

The &quot;Id&quot; numbers are in a comment field? Do you mean &quot;memo field or just a regular text field nammed &quot;comment&quot;?

You state &quot;Fortunately, the ID's are mostly integers&quot;, but can you describe EXACTLY how the Id Numbers are delimited?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JonP46]

The IDs are in a text field -- 160 chars long. They are delilmited haphazardly: some by spaces (203 909 234) or by commas (203, 909, 234) or by dashes (203-909-234) with a number of other variations (#203, #909, #234) and a number of punctuation errors (203 ,909 ,234).

There is also military time recored in the text field -- 0909, 1234, 1203. Simply querying for &quot;like 203&quot; also returns 1203.

I think I have a pretty good filter built -- ie:
where comment like &quot;* 203 *&quot;
where comment like &quot;* 203, *&quot;
where comment like &quot;* ,203, *&quot;
where comment like &quot;* ,203 *&quot;
where comment like &quot;* #233 *&quot;
. . .
on down the line


I need to be able to loop through 1 to 2000, using the value of the loop iteration as an argument or variable to place in the query. On comment field can have several different IDs. There are close to 30,000 records.

I am finding that strings within strings in Access is pretty wierd.

Thanks for your help.

Jon

 

[ghubbell]

As this is not a Memo field, how about doing a find/replace to set a constant delimiter? Find &quot;-&quot; Replace with &quot;,&quot; etc. till all are in sync. From there you could begin to strip columns out cleanly. Backup the table before you start in the event of error... Gord
ghubbell@total.net

 

[MichaelRed]

JonP46,

Some 'leap of faith' is embeded here. By investigation, I see that the &quot;ID&quot; is always a three digit code surrounded by some valid delimiter(s). So the first routine should 'walk' threough the field, getting three characters at a time. If these are not numeric, we don't care as they cannot be a valid ID. If they are, we call a routine to check the (remaining) criteria.

CheckId first gets the characters preceeding an following the potential ID. It then checks to see if they are numericss. If either is, then the potential Id is just part of a longer numeric string (Military time), so it is not a real ID and we exit.

If we get past this part, we still need to check that at least one of the delimiters exist, so we just loop through the delimiters, checking for a delimiter as the preceeding or following characters. If we find even one, then we have the match and return with the value indicating that the nmatch is found.

I do not have a database table/field to test this on, so I'm SURE that some part of it is mis-coded (at least a typo or several). You need to step through this and note the errors. If there are some you cannot fix, please post, with the complete error and the line where it occurs.

If you can post a SMALL sample of your table (actually just a few of the comment fields) I will go a bit further with it. this should be close enough to get you looknig at vb syntax. Obviously, you need to change &quot;Your ... Here&quot; to reflect your database & field names.

Public Function basGetMultiIds()

    Dim dbs As Database
    Dim rst As Recordset

    Dim Idx As Integer
    Dim IsId As Integer
    Dim TryIdx As String * 3
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("YourTableNameHere", dbOpenDynaset)

    While Not rst.EOF
        MyFld = rst!YourCommentFieldNameHere    'Get the field into a common var

        For Idx = 1 To Len(MyFld) - 2
            TryIdx = Mid(MyFld, Idx, 3)         'Get Three chars

            If (IsNumeric(TryIdx)) Then         'See if they are a number
                IsId = CheckID(MyFld, Idx)      'Check for 'proper delimiter(s)
    
                If (IsId) Then
                    'Create New Record Here
                    'Note: If IsId is true (= -1)
                    'TryIdx is a 'valid' ID
                End If
    
            End If

        Next Idx

    rst.MoveNext

    Wend

End Function
Public Function CheckID(MyFld As String, Idx As Integer) As Integer

    'Here we just check that the three characters
    'preceeding Idx in MyFld are 'properly' delimited.
    'by properly delimited, we MEAN:
    'NOT a four digit number and having at least ONE delimiter
    'Preceeding or following the three digit 'numner.

    Dim Jdx As Integer
    Dim MyChr(1) As String * 1
    Dim DelimChr(20) As String * 1

    DelimChr(0) = Space(1)
    DelimChr(1) = "."
    DelimChr(2) = ","
    DelimChr(3) = ";"
    DelimChr(4) = "-."
    DelimChr(5) = "#"
    DelimChr(6) = ")"
    DelimChr(7) = "("
    DelimChr(8) = ""

    DelimChr(20) = "&"

    If (Idx > 3) Then                       'Check for preceeding
        MyChr(0) = Mid(MyFld(Idx - 3))      'Get Preceeding Char
    End If

    If (Idx < Len(MyFld)) Then              'Check for Following
        MyChr(1) = Mid(MyFld(Idx + 1))      'Get Following Char
    End If

    For Jdx = 0 To 1
        If (IsNumeric(MyChr(Jdx))) Then     'Check for a numeric
            Exit Function                   'So Exit
        End If
    Next Jdx

    'Arrive here, At least it is not embeded in four (or more) digit string
    For Jdx = 0 To UBound(DelimChr)
        If (DelimChr(Jdx) <> &quot;&quot;) Then
            For Kdx = 0 To 1
                If (DelimChr(Jdx) = (MyChr(Kdx))) Then  'Check for a Delimiter
                    CheckID = -1                        'Hit implies Valid
                    Exit Function                       'So Exit
                End If
            Nextr Kdx
    Next Jdx

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over