Split function in Access 97

[bishman]

I've got some VBA code in an Access 2000 database that works fine. However, when I convert it to Access 97 one of the functions I've used (Split) fails.

The code is:

monthAndYear = "April 2003"
month = Split(monthAndYear, " &quot

This gives month a value of "April".

Is there a function or bit of code that would work in Access 97 (i.e. something that doesn't use Split) to do the same thing?

TIA
bishman

 

[jrbarnett]

month = Mid$ (monthandyear, 1, instr (monthandyear, " ", 1)-1)

John

 

[oharab]

The split function takes a character seperated string and returns it as an array.
To replicate this in A97 use this function:

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


B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[bishman]

Thanks Ben, that works a treat.