Splitting string

[John]

Hi

Is there a way to split a multi-line address field into individual lines of
address in access 97?

Thanks

Regards

 

[Nick Coe \(UK\)]

If you can test for a specific character(s) like CrLf or
comma or whatever where you want the split to occur then
yes.

The functions you might need to look up in the Help are
InStr(), Mid(), Left(), Right() and the Trim functions
LTrim, RTrim and Trim.

Then you parse through the contents of the field char by
char putting each chunk into another field.

Access 97 doesn't include the Split() function which is
present in A2000 and later unfortunately.

Hope that helps.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In John typed:

 

[Douglas J. Steele]

As Nick correctly points out, there are a number of string-related functions
(including Split) not present in Access 97.

However, you can use what's in http://support.microsoft.com/?id=188007 as a
starting point to add your own equivalent functions.

One change you'll have to make to that code. Access 97 doesn't allow Enums,
so anywhere the function declaration has

Optional bCompare As VbCompareMethod = vbBinaryCompare

you'll need to change it to

Optional bCompare As Long = vbBinaryCompare

That KB article doesn't have the world's greatest code in it, but, as I
said, it's a good starting point.

 

[Dirk Goldgar]

Hi

Is there a way to split a multi-line address field into individual
lines of address in access 97?

Here's an Access 97 version of the VB6 Split function I had lying
around. The original code was written and posted by John Viescas. I
made a number of modifications.

'----- start of code -----
Public Function Split( _
Expression As String, _
Optional Delimiter As String = " ", _
Optional ByVal Limit As Long = -1, _
Optional ByVal Compare As Integer = 0) _
As Variant
'-----------------------------------------------------------
' Inputs: String to search,
' delimiter string,
' optional replacement limit (default = -1 .. ALL)
' optional string compare value (default vbBinaryCompare)
' Outputs: Array containing items found in the string
' based on the delimiter provided
' Original code by: John L. Viescas 5-Sep-2001
' Extensively revised by: Dirk Goldgar 21-Jan-2002
' Last Revision: Dirk Goldgar 21-Jan-2002
' ** Duplicates the functionality of the VB 6 SPLIT function.
'-----------------------------------------------------------
Dim lngCnt As Long
Dim intIndex As Integer
Dim lngPos As Long
Dim lngI As Long
Dim strArray() As String

If (Compare < -1) Or (Compare > 2) Then
Err.Raise 5
Exit Function
End If
' If count is zero, return an empty array
If Limit = 0 Then
Split = Array()
Exit Function
End If
' If the Delimiter is zero-length, return a 1-entry array
If Len(Delimiter) = 0 Then
ReDim strArray(0)
strArray(0) = Expression
Split = strArray
Exit Function
End If

' Start count at (Limit - 1) because function returns
' whatever is left at the end.
lngCnt = Limit - 1
' Start scanning at the start of the string.
lngPos = 1
' Loop until the counter is zero.
Do Until lngCnt = 0
lngI = InStr(lngPos, Expression, Delimiter, Compare)
' If the delimiter was not found, end the loop.
If lngI = 0 Then Exit Do
' Add 1 to the number returned.
intIndex = intIndex + 1
' Expand the array to fit in a new element.
ReDim Preserve strArray(0 To intIndex - 1)
' Use index - 1 .. zero-based array
strArray(intIndex - 1) = Mid$(Expression, lngPos, lngI - lngPos)
' Advance past the found entry and the delimiter.
lngPos = lngI + Len(Delimiter)
lngCnt = lngCnt - 1
Loop
' Everything after the last delimiter found goes in the last entry
' of the array.
intIndex = intIndex + 1
ReDim Preserve strArray(0 To intIndex - 1)
If lngPos <= Len(Expression) Then
strArray(intIndex - 1) = Mid$(Expression, lngPos)
Else
strArray(intIndex - 1) = vbNullString
End If

' Return the result
Split = strArray

End Function
'----- end of code -----