JonP46,
Some 'leap of faith' is embeded here. By investigation, I see that the "ID" 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 "Your ... Here" to reflect your database & field names.
Code:
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) <> "") 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
