Private Type Rec
MemberID As Long
Boat As String
StartDate As Variant
EndDate As Variant
End Type
Public Sub SplitBoats(ByVal StrBoats As String, _
ByRef Record As Rec)
Dim varBoats As Variant
Dim varRecord As Variant
Dim varItem As Variant
Dim strBoat As String
Dim varStart As Variant
Dim varEnd As Variant
Dim varDates As Variant
' Clean up
StrBoats = Replace(StrBoats, " (", "(")
StrBoats = Replace(StrBoats, " ,", ",")
' first attempt
varBoats = Split(StrBoats, ", ")
If UBound(varBoats) = 0 Then
' second attempt
varBoats = Split(varBoats(0), " ")
End If
' final check to get individual values and call to INSERT
For Each varItem In varBoats
If varItem Like "*(##*" Then
varRecord = Split(varItem, "(")
Record.Boat = varRecord(0)
' tidy up and get date(s)
varRecord(1) = Replace(varRecord(1), ")", "")
varDates = Split(varRecord(1), "-")
Record.StartDate = varDates(0)
If UBound(varDates) > 0 Then
Record.EndDate = varDates(1)
End If
Else
Record.Boat = varItem
End If
' pass Record to a function that builds the SQL string and INSERTs
Call InsertRecord(Record)
' reset variables for next run
With Record
.Boat = vbNullString
.StartDate = Empty
.EndDate = Empty
End With
Next
End Sub