DZ said:
I'm using Access 97 and I have Visual Basic 6 installed on my machine,
Does
anyone know what library I need to set a reference to, to be able to use
some
Visual Basic 6 functions like Split. (A VB6 text function)
I tried adding references to
Microsoft Visual Basic 6 Extensibility
Visual Basic objects and Procedures
But these did not make Split work
If you know the exact .OLB, .DLL file name, that would be most helpful so
I
can browse for it from the Access references dialog
Is Split function even possible in Access??
I don't believe you can use any of the new VB6 functions in Access 97,
though they became available with Access 2000. Various people have written
replacements for them. I use this implementation of the Split function:
'----- 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 -----
