Hi Will,
To use the replace function, you need to find out the
ASCII code of the character (you can use the asc()
function) and use the chr() function to specify it in
other functions (such as replace() or instr()). In other
words, instead of typing something such as "t" (which
would replace the t's), you would type chr(10) (or
whatever your ASCII code is - mine is 10 in the Oracle db
that I link to) to specify the string that you want to
replace.
Oracle uses these to delimit array fields. If you like
you can also write a custom function to return just
one "field" of the array. One that I use is pasted below
if you are interested (titled parsearray()). To call
this function, you just enter the field reference, the
number of the "field" number that you want to return, and
optionally the ascii chr number of the delimiter
(defaults to 10) and "Y" or "N" to specify whether to add
a prefix of the field number and a dash to the string
(defaults to no). You can use this function to break the
contents of the array field into separate columns in a
query if you have that need.
Here's the code:
Public Function ParseArray(InputArrayField, OutputFieldNo
As Integer, Optional DelimiterCharCode As Integer = 10,
Optional InclFieldNo_Y_N As String = "N")
Dim ArrayBreakPos() As Integer, strPrefix As String
On Error GoTo ErrorHandler
If IsNull(InputArrayField) = True Then
ParseArray = Null
Exit Function
End If
If UCase(Left(InclFieldNo_Y_N, 1)) = "Y" Then
strPrefix = OutputFieldNo & " - "
Else
strPrefix = ""
End If
ReDim ArrayBreakPos(OutputFieldNo) As Integer
'Set the Break Position 0 (which is not a real break) as
0 to start searching the string initially
'At the start point of the string (1 character after the
previous break pos)
ArrayBreakPos(0) = 0
For i = 1 To OutputFieldNo
ArrayBreakPos(i) = InStr(ArrayBreakPos(i - 1) + 1,
InputArrayField, Chr(DelimiterCharCode), vbTextCompare)
If ArrayBreakPos(i) = 0 Then
If i < OutputFieldNo Then
ParseArray = Null
Else
ParseArray = strPrefix & Right
(InputArrayField, Len(InputArrayField) - ArrayBreakPos
(OutputFieldNo - 1))
End If
Exit Function
End If
Next
ParseArray = strPrefix & Mid(InputArrayField,
ArrayBreakPos(OutputFieldNo - 1) + 1, ArrayBreakPos
(OutputFieldNo) - ArrayBreakPos(OutputFieldNo - 1) - 1)
Exit Function
ErrorHandler:
ParseArray = "Error - " & Err.Number & " " &
Err.Description
End Function
Hope that helps. Post back if it doesn't, or if you have
further questions.
-Ted Allen
