G
G13m
Hi,
I have a field from a query that uses linked tables from a firebird
database. The field in question is linked as a memo data type in Access. The
data can be several hundred characters long in each record of this field. I
am taking the data in this field and putting into a string type variable.
Then I am using the Split() function to break up individual words and then
append to another table with each individual word. The problem is that after
roughly 255 characters the string loaded from the current record in the
recordset just has garbage. I say roughly because I did a character count and
where the garbage characters start seems to vary depending on the record. I
suspect that the entire string isn't being loaded from the recordset into the
split function. It's hard to tell because the "watch" for the value in the
field won't show the entire string anyway.
The code is below:
Private Sub cmdParseTest_Click()
On Error GoTo Err_cmdParseTest_Click
Dim stDocName As String
Dim qryAppend As QueryDef
Dim rs As Recordset
Dim db As Database
Dim parsed() As String
Dim arrayCounter As Integer
DoCmd.SetWarnings False
DoCmd.Hourglass True
Set db = CurrentDb()
stDocName = "zqdeltblParsedShipSerialLog"
'Clear the output table that is appended to
DoCmd.OpenQuery stDocName, acNormal, acEdit
Set rs = db.QueryDefs("qryShipSerials_003").OpenRecordset 'Open
the query that contains the field to split
Set qryAppend = db.QueryDefs("zqappSerialParse")
'Query that appends using the split words from the array
While Not rs.EOF
parsed() = Split(rs.Fields("SHIPNOTES"))
For arrayCounter = LBound(parsed()) To UBound(parsed())
'Append using the split words for each criteria
qryAppend.Parameters("JOBNOIn") = rs.Fields("JOBNO")
qryAppend.Parameters("SHIPDATEIn") = rs.Fields("SHIPDATE")
qryAppend.Parameters("REFIDIn") = rs.Fields("REFID")
qryAppend.Parameters("SERIALIn") = parsed(arrayCounter)
qryAppend.Execute
Next
rs.MoveNext
Wend
Exit_cmdParseTest_Click:
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Sub
Err_cmdParseTest_Click:
MsgBox Err.Description
Resume Exit_cmdParseTest_Click
End Sub
This is the code for the split function (found the code online):
Public Function Split(ByVal InputText As String, _
Optional ByVal Delimiter As String) As String()
' This function splits the sentence in InputText into
' words and returns a string array of the words. Each
' element of the array contains one word.
' This constant contains punctuation and characters
' that should be filtered from the input string.
Const CHARS = ".!?,;:""'()[]{}" & vbCrLf
Dim strReplacedText As String
Dim intIndex As Integer
' Replace tab characters with space characters.
strReplacedText = Trim(Replace(InputText, _
vbTab, " "))
' Filter all specified characters from the string.
For intIndex = 1 To Len(CHARS)
strReplacedText = Trim(Replace(strReplacedText, _
Mid(CHARS, intIndex, 1), " "))
Next intIndex
' Loop until all consecutive space characters are
' replaced by a single space character.
Do While InStr(strReplacedText, " ")
strReplacedText = Replace(strReplacedText, _
" ", " ")
Loop
' Split the sentence into an array of words and return
' the array. If a delimiter is specified, use it.
'MsgBox "String:" & strReplacedText
If Len(Delimiter) = 0 Then
Split = VBA.Split(strReplacedText, " ")
Else
Split = VBA.Split(strReplacedText, Delimiter)
End If
End Function
I have a field from a query that uses linked tables from a firebird
database. The field in question is linked as a memo data type in Access. The
data can be several hundred characters long in each record of this field. I
am taking the data in this field and putting into a string type variable.
Then I am using the Split() function to break up individual words and then
append to another table with each individual word. The problem is that after
roughly 255 characters the string loaded from the current record in the
recordset just has garbage. I say roughly because I did a character count and
where the garbage characters start seems to vary depending on the record. I
suspect that the entire string isn't being loaded from the recordset into the
split function. It's hard to tell because the "watch" for the value in the
field won't show the entire string anyway.
The code is below:
Private Sub cmdParseTest_Click()
On Error GoTo Err_cmdParseTest_Click
Dim stDocName As String
Dim qryAppend As QueryDef
Dim rs As Recordset
Dim db As Database
Dim parsed() As String
Dim arrayCounter As Integer
DoCmd.SetWarnings False
DoCmd.Hourglass True
Set db = CurrentDb()
stDocName = "zqdeltblParsedShipSerialLog"
'Clear the output table that is appended to
DoCmd.OpenQuery stDocName, acNormal, acEdit
Set rs = db.QueryDefs("qryShipSerials_003").OpenRecordset 'Open
the query that contains the field to split
Set qryAppend = db.QueryDefs("zqappSerialParse")
'Query that appends using the split words from the array
While Not rs.EOF
parsed() = Split(rs.Fields("SHIPNOTES"))
For arrayCounter = LBound(parsed()) To UBound(parsed())
'Append using the split words for each criteria
qryAppend.Parameters("JOBNOIn") = rs.Fields("JOBNO")
qryAppend.Parameters("SHIPDATEIn") = rs.Fields("SHIPDATE")
qryAppend.Parameters("REFIDIn") = rs.Fields("REFID")
qryAppend.Parameters("SERIALIn") = parsed(arrayCounter)
qryAppend.Execute
Next
rs.MoveNext
Wend
Exit_cmdParseTest_Click:
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Sub
Err_cmdParseTest_Click:
MsgBox Err.Description
Resume Exit_cmdParseTest_Click
End Sub
This is the code for the split function (found the code online):
Public Function Split(ByVal InputText As String, _
Optional ByVal Delimiter As String) As String()
' This function splits the sentence in InputText into
' words and returns a string array of the words. Each
' element of the array contains one word.
' This constant contains punctuation and characters
' that should be filtered from the input string.
Const CHARS = ".!?,;:""'()[]{}" & vbCrLf
Dim strReplacedText As String
Dim intIndex As Integer
' Replace tab characters with space characters.
strReplacedText = Trim(Replace(InputText, _
vbTab, " "))
' Filter all specified characters from the string.
For intIndex = 1 To Len(CHARS)
strReplacedText = Trim(Replace(strReplacedText, _
Mid(CHARS, intIndex, 1), " "))
Next intIndex
' Loop until all consecutive space characters are
' replaced by a single space character.
Do While InStr(strReplacedText, " ")
strReplacedText = Replace(strReplacedText, _
" ", " ")
Loop
' Split the sentence into an array of words and return
' the array. If a delimiter is specified, use it.
'MsgBox "String:" & strReplacedText
If Len(Delimiter) = 0 Then
Split = VBA.Split(strReplacedText, " ")
Else
Split = VBA.Split(strReplacedText, Delimiter)
End If
End Function