MSAccess97 uses a VBA that doesn't have the functions Join, Split, nor Replace. Add the functions below to your project. Together, they create a Replace.
A.E.Veltstra
28 May 2002
Fully reviewed at 11 Dec. 2012
Mr. Chad M. Kovac advised to change the Split function. The splitter used to be limited to one character. Now, it can contain any length.
Split() is used to separate a text (character string). Provide the string to split up, as well as the character that acts as the seperator. It returns a variable String-Array (in VBA-terminologie of 1997 this is a Variant). This array will contain the split-up pieces of text, without the splitter character.
Join() is used to combine a String-array of texts. Pass the array of strings that require combining, as well as the character that acts as glue (it may be an empty string). The function yields a joined, single string.
Split() is used to separate a text (character string). Provide the string to split up, as well as the character that acts as the seperator. It returns a variable String-Array (in VBA-terminologie of 1997 this is a Variant). This array will contain the split-up pieces of text, without the splitter character.
Public Function Split(ByVal strSource As String, _
ByVal strSplitter As String) As Variant
On Error GoTo splitError
Dim varArray() As String
Dim lngPosStart As Long, lngPosStop As Long, lngSourceLength As Long
lngSourceLength = Len(strSource)
If (lngSourceLength > 0) Then
If (Len(strSplitter) > 0) Then
If (InStr(1, strSource, strSplitter) > 0) Then
ReDim varArray(0)
lngPosStart = 1
'all elements in front of the splitter
lngPosStop = InStr(lngPosStart, strSource, strSplitter)
Do While ((lngPosStop > 0) And (lngPosStart <= lngSourceLength))
varArray(UBound(varArray)) = Mid(strSource, lngPosStart, _
(lngPosStop - lngPosStart))
ReDim Preserve varArray(UBound(varArray) + 1)
lngPosStart = (lngPosStop + Len(strSplitter)) 'recent change
lngPosStop = InStr(lngPosStart, strSource, strSplitter)
Loop
'the element after the last splitter
If (lngSourceLength >= lngPosStart) Then
varArray(UBound(varArray)) = Mid(strSource, lngPosStart, _
((lngSourceLength - lngPosStart) + 1))
Else 'remove empty element at the end
ReDim Preserve varArray(UBound(varArray) - 1)
End If
Split = varArray
Else
Split = strSource
End If
Else
Split = strSource
End If
Else
Split = ""
End If
splitError:
If (Err.Number <> 0) Then
Split = strSource
Err.Clear
End If
End Function
Join() is used to combine a String-array of texts. Pass the array of strings that require combining, as well as the character that acts as glue (it may be an empty string). The function yields a joined, single string.
Public Function Join(ByVal varArray As Variant, _
ByVal strJoiner As String) As String
On Error GoTo joinError
Dim lngMin As Long, lngMax As Long, lngCounter As Long, strBuffer As String
Dim strElement As String, lngElementLength As Long, lngStart As Long
Join = ""
strBuffer = String(1000, Chr(0))
If (IsArray(varArray)) Then
lngMin = LBound(varArray)
lngMax = UBound(varArray)
lngStart = 1
For lngCounter = lngMin To lngMax
If (Len(strBuffer) < lngStart) Then 'adjust bufferlength if necessary
strBuffer = strBuffer & String(1000, Chr(0))
End If
strElement = varArray(lngCounter) & strJoiner
lngElementLength = Len(strElement)
Mid(strBuffer, lngStart, lngElementLength) = strElement
lngStart = lngStart + lngElementLength
Next
'cut buffer to size: ((lngStart - 1) - strJoiner)
Join = Left(strBuffer, ((lngStart - 1) - Len(strJoiner)))
End If
joinError:
If (Err.Number <> 0) Then
Join = ""
Err.Clear
End If
End Function
Need problem solving?
Talk to me. Let's meet for coffee or over lunch. Mail me at “omegajunior at protonmail dot com”.